How to write SQL statements in succession without error

Question

I am running a CREATE VIEW statement which is perfectly self-contained and runs perfect on its own. Two lines below I run a separate SELECT statement to view the results, which works perfect on its own.

However, I run the whole script together and it doesn't work.

Code looks something like this:

--- These four lines run fine on their own.
CREATE OR ALTER VIEW view1 AS
SELECT column1, column2, column3
FROM table1;

--- This line runs fine on its own
SELECT * FROM view1

--- If I run the whole script it doesn't work

The error message is "Incorrect syntax near the keyword 'SELECT'."

Separate the batches. Add a GO after each statement. https://techdifferences.com/difference-between-ddl-and-dml-in-dbms.html — Shawn, May 30 '19 at 19:10
@MihaiChelaru Not really a duplicate. It's kind of approaching the same answer from a different angle. Granted, the accepted answer there does give a good explaination as to why `GO` is needed in this instance. — Shawn, May 30 '19 at 19:42
@Shawn While perhaps not an exact duplicate, the answers directly solve the problem here, and this sort of question has been asked multiple times. See also the following questions: [In SQL Server, when should you use GO and when should you use semi-colon ;?](https://stackoverflow.com/q/3701147/9374673) and [What is the difference between “;” and “GO” in T-SQL?](https://stackoverflow.com/q/1517527/9374673). — Mihai Chelaru, May 30 '19 at 20:02
Thanks for the answers. I now see it's a possible duplicate to other questions I didn't know how to find, because I didn't know about the existence of the GO statement — Alan, May 30 '19 at 20:17

score 2 · Answer 1 · answered May 30 '19 at 19:14

2

You need to put a GO between the statements. The semicolon is insufficient to tell the parser that they are separate batches.

answered May 30 '19 at 19:14

jmoreno

12,752
4
60
91

score 0 · Answer 2 · answered May 30 '19 at 19:08

You should run this queries separetly or you have too old SQL server to run CREATE OR ALTER VIEW. Firstly create the view, and then run the select.

You can also add GO, it will separate batches:

--- These four lines run fine on their own.
CREATE OR ALTER VIEW view1 AS
SELECT column1, column2, column3
FROM table1;
GO
--- This line runs fine on its own
SELECT * FROM view1

--- If I run the whole script it doesn't work

score 0 · Answer 3 · answered May 30 '19 at 19:12

Add batch separator, Go, stmt between, End of create view stmt and your select from view.

also will need to add check if view exists and drop it first stmt or change to alter or create view depending on version of sql.

IF OBJECT_ID('FOO') IS NOT NULL
DROP VIEW FOO;
GO

CREATE VIEW FOO
AS
SELECT * FROM sysobjects;
GO
SELECT * FROM FOO
GO

How to write SQL statements in succession without error

3 Answers3