Posted by Erland Sommarskog on 02/13/07 22:17

BJMurphy (murphy.ben@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]