|
|
Posted by BJMurphy on 02/14/07 14:36
On Feb 13, 5:17 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> BJMurphy (murphy....@gmail.com) writes:
> > I am used to other SQL engines, and have a few basic questions--
>
> > 1)If I wanted to conditionally drop a table, does SQL Server have a
> > way to natively do this? Many SQL implementations will allow
> > something like:
>
> > CREATE OR REPLACE tablename AS
> > SELECT
> > x,y,z
> > FROM sourcetable
> > ;
>
> > Does SQL Server have something like this? This syntax, both the
> > "create table as select" syntax and the "create or replace" syntax
> > seem to cause problems.
>
> You can create a table from a query this way:
>
> SELET x, y, z
> INTO tablename
> FROM sourcetable
>
> There is nothing resemblent of CREATE OR REPLACE for anything in SQL
> Server. You need to have things like:
>
> IF object_id('sometable') IS NOT NULL
> DROP TABLE sometable
>
> > 2) Some of our existing queries have a keyword, "GO" where I would
> > otherwise expect a semi-colon. Is there a functional difference
> > between the two? I seem to be able to replace the "GO" keywords with
> > semi-colons without any changes in how the script behaves, but I
> > thought I would check and see if anyone has advice about the
> > differences here.
>
> GO and ; are entirely unrelated. In T-SQL, GO is just another
> identifier like MyTable, [Order Details] or whatever. GO is used by
> many query tools as a batch separator, so that you can give the tool
> something like:
>
> CREATE something
> go
> -- Use this something
> go
> -- Drop this something
>
> Each batch will be sent to SQL Server separately. A batch can consist
> of many statements, separated or not by semicolons.
>
> In application code you would typically never use GO, but each Execute
> command is a batch.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -
>
> - Show quoted text -
Thanks, Erland, for your response.
To clarify further, if I use a semi-colon at the end of my query, and
put several queries in a row, SQL server will not execute them until
it reaches the end of the set of queries or a "GO" statement. Is that
correct?
Put another way, if I am creating table A in one query and then want
to use it in the next query, do I have any options other than using
"GO" after the first query to ensure that table A will be available in
the second query?
I understood the semi-colon to be analogous to "GO" in that both would
submit the query to the SQL server, acting as a separator, but it
seems that you are saying this is not the case. I apologize for my
confusion, I just want to make sure I have everything straight in my
head.
[Back to original message]
|