Executing Multi-lines SQL Scripts From Command-line

[James]

Hello,

I'm trying to execute a file containing few SQL statements.

CREATE VIEW test1 AS SELECT * FROM table1;
CREATE VIEW test2 AS SELECT * FROM table2;

The standard SQL way is to end a statement with semi-colon.
But doing that,it doesn't work in SQL Server.
After changing ";" to "GO", it works fine.

Is there anyway we can stick to ";" to indicate the end of statement.
I don't want to create scripts which works only in SQL Server.

Please comment.

Thanks in advance.

[Erland Sommarskog]

Simon Hayes (sq*@hayes.ch) writes:

Neither the semi-colon nor GO are 'standard' SQL. GO is recognized by the
SQL Server client tools as a batch delimiter. The semi-colon is the Oracle
equivalent, as far as I know.

And the ANSI equivalent. Hey, have you never seen Joe Celko's postings?
He has semi-colons all over the place.

Semicolon as a statement terminator is indeed standard SQL, and it is a
pity that Sybase way back in the 1980s settled on a semicolon-free syntax.
Microsoft added semicolons as a optional terminator in SQL7, but it would
constitute a major blow to existing code to make it mandatory. (But if
MS would supply a tool that added all missing semicolons to existing
code, it could be worth the effort.)

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

James (eh****@hotmail.com) writes:

I'm trying to execute a file containing few SQL statements.

CREATE VIEW test1 AS SELECT * FROM table1;
CREATE VIEW test2 AS SELECT * FROM table2;

The standard SQL way is to end a statement with semi-colon.
But doing that,it doesn't work in SQL Server.
After changing ";" to "GO", it works fine.

Is there anyway we can stick to ";" to indicate the end of statement.
I don't want to create scripts which works only in SQL Server.

This is not legal T-SQL:

CREATE VIEW test1 AS SELECT * FROM table1;
go
CREATE VIEW test2 AS SELECT * FROM table2;
go

For some explicable reason ; is not permitted here. (Probably because
CREATE VIEW must be alone in a batch.

However, if you change the batch separator to with the -c option as
Simon Hayes suggested, this works:

CREATE VIEW test1 AS SELECT * FROM table1
;
CREATE VIEW test2 AS SELECT * FROM table2
;

And it still legal in ANSI-compliant engines.

Since the batch-separator must be alone on a line, this solution
can work decently. Of course if a developer for some reason puts a
lone semicolon in the middle of a stored procedure, he effectively
splits that procedure in two.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Simon Hayes]

"Erland Sommarskog" <so****@algonet.se> wrote in message
news:Xn**********************@127.0.0.1...

Simon Hayes (sq*@hayes.ch) writes:

Neither the semi-colon nor GO are 'standard' SQL. GO is recognized by the SQL Server client tools as a batch delimiter. The semi-colon is the Oracle equivalent, as far as I know.

And the ANSI equivalent. Hey, have you never seen Joe Celko's postings?
He has semi-colons all over the place.

Semicolon as a statement terminator is indeed standard SQL, and it is a
pity that Sybase way back in the 1980s settled on a semicolon-free syntax.
Microsoft added semicolons as a optional terminator in SQL7, but it would
constitute a major blow to existing code to make it mandatory. (But if
MS would supply a tool that added all missing semicolons to existing
code, it could be worth the effort.)

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Interesting, I didn't know there was any standard at all in that area. It's
a good point about Celko's posts, though, given his insistence on
platform-independent code - I guess I should have worked that one out...

Simon