SQL script which only runs after the addition of 'GO' command

Question

I know lots have already been written about what GO does and when to use it but I haven't seen anything which explains the following scenario.

We have been supplied with some stored procedures with an application to populate a set of static warehouse tables, but they were producing error messages. Each procedure has the following structure:

exec usp_CreateDWTable

insert into dbo.DWTable
Select [somefields]
from [SomeLiveTables]

The usp_CreateDWTable stored procedure consists of:

if exists (select * from sysobjects where id = object_id('dbo.DWTable') and sysstat & 0xf = 3)
drop table dbo.DWTable

CREATE TABLE dbo.DWTable(CONSTRAINT [field1] PRIMARY KEY (Alert_ID),[field2],,,)

Running as above returns the error message: Msg 213, Level 16, State 1, Line 15 Column name or number of supplied values does not match table definition.

I have eventually worked out that adding GO between the EXEC and the INSERT fixes the issue but if sql process commands sequentially anyway what difference does this make? Now I know a CREATE TABLE batch has to be sent to the server before you can send an INSERT but I have read that sql can handle this scenario implicitly, is the fact that the CREATE TABLE is in a sp interfering with this?

Even if my above assumption above is correct, an additional source of confusion is that we have two instances of this database on the server and this error does not happen on the other database. The structures of both databases are virtually identical, although the data is different. Is there a setting within the database that is causing them to behave differently?

Thanks

GO is a SSMS/sqlcmd keyword (it's not a T-SQL operator), so i doubt that's the solution. The message above says the error is on Line 15, but we only have 4 lines. I suspect the problem is your INSERT and that you aren't declaring the columns in your INSERT clause. — Thom A
– Thom A ♦, Commented Jul 11, 2019 at 8:52
There were comments above the code, which I didn't include here, so apologies for that not lining up. The line number in the error corresponds to the INSERT command. All the necessary columns are included, indeed running the EXEC and INSERT statements separately (i.e. highlighting each and clicking execute) working fine. I assume doing that replicates the same behaviour as the GO keyword..? — MarkB
– MarkB, Commented Jul 11, 2019 at 9:04

Dan Guzman · Accepted Answer · 2019-07-11 10:37:58Z

2

The entire batch is compiled using the existing table definition when the table already exists. So when the batch includes drop/create/insert, the batch is validated against the existing schema before the new table is created and compilation fails when the existing/new table has a different number of columns.

When you execute the script as separate batches (GO command), the drop/create statements are executed first. Then the insert statement batch is then compiled and run using the new table definition.

answered Jul 11, 2019 at 10:37

Dan Guzman

46.8k3 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MarkB Over a year ago

Thanks for the reply. You're right. I missed the fact that it adds some extra fields to the table after the main insert statement. So it is validating the insert statement against the current version of the table (with extra fields), that makes sense. So how am I not getting this error in the other database? The sp is exactly the same. Interestingly, if I try to look at the estimated execution plan I get the error as mentioned, but if i just execute it works fine. Is it using a cached execution plan?

Dan Guzman Over a year ago

@MarkB, you won't get the error if either 1) the table exists and has the same number of columns and conformant data types or 2) the table does not exist at all (in which case the statement complilation is deferred until execution time).

MarkB Over a year ago

That makes sense but I don't think either of those are satisfied. 1) because of the extra fields added afterwards (although it works in practice as the table will be dropped and recreated without those fields) and 2) I can see it. I feel like I'm still missing part of the puzzle. I've noticed that if I execute it as a sp it runs, however if I take the constituent SQL from the sp, paste it to a new window and execute, I get the error. Is there a difference in how procedures are validated compared to t-sql in the query window? It's like one database is validating the sp and the other isn't.

Collectives™ on Stack Overflow

SQL script which only runs after the addition of 'GO' command

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related