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]

Re: Executing Multi-lines SQL Scripts From Command-line

Simon Hayes (sql@hayes.ch) writes:[color=blue]
> 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.[/color]

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, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Erland Sommarskog]

Re: Executing Multi-lines SQL Scripts From Command-line

James (ehchn1@hotmail .com) writes:[color=blue]
> 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.[/color]

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, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Simon Hayes]

Re: Executing Multi-lines SQL Scripts From Command-line

"Erland Sommarskog" <sommar@algonet .se> wrote in message
news:Xns93D3E36 904A63Yazorman@ 127.0.0.1...[color=blue]
> Simon Hayes (sql@hayes.ch) writes:[color=green]
> > Neither the semi-colon nor GO are 'standard' SQL. GO is recognized by[/color][/color]
the[color=blue][color=green]
> > SQL Server client tools as a batch delimiter. The semi-colon is the[/color][/color]
Oracle[color=blue][color=green]
> > equivalent, as far as I know.[/color]
>
> 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, sommar@algonet. se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]

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