Breaking up insert statements with GO

Question

I'm trying to build off of this answer How do I run a large script with many inserts without running out of memory?

and break up a query into smaller ones with BEGIN TRANSACTION and then GO's

BEGIN TRANSACTION; 

MERGE ghcnd.dbo.us_APCP as target 
using 
(values 
  ('US1CASN0123','2018-03-22','--N','251'),
  ('US1KSGO0013','2018-03-22','--N','41'),
  ('US1WYFM0039','2018-03-22','--N','0'),
  ('US1SCCF0008','2018-03-22','--N','10')) 
as source(cell,[date],valueFlag,[value]) 
on target.cell = source.cell 
AND target.date = source.date  
when matched then 
    update 
    set valueFlag = source.valueFlag , 
        value = source.value 
when not matched then 
    insert (cell,[date],valueFlag,[value]) 
    values (cell,[date],valueFlag,[value]) 

COMMIT TRANSACTION; GO

However I get this error

Msg 102, Level 15, State 1, Line 3 Incorrect syntax near ')'. Msg 102, Level 15, State 1, Line 3 Incorrect syntax near 'GO'.

Solomon Rutzky · Accepted Answer · 2018-05-04T20:00:48.250

PROBLEM NUMERO ONE

GO is the default query batch separator. It is a feature of certain client utilities (mainly SQLCMD and SSMS) and is unknown to the SQL Server engine itself. GO needs to be on a line by itself (optionally followed by only an INT value to cause repetition of that batch) as it is parsed by these client utilities to indicate where to split up what is being submitted to SQL Server.

It cannot be included in any Dynamic SQL as it is, again, unknown to SQL Server. It is also unnecessary to include in Dynamic SQL since each call to EXEC() is its own query batch.

So,

If you are generating a SQL script to exec via SSMS or SQLCMD, then simply put a newline / CRLF between COMMIT TRANSACTION; and GO
If you are generating Dynamic SQL to run each of these blocks via an EXEC(@SQL) call, then simply remove the GO.

PROBLEM NUMERO TWO

After fixing the GO situation, you will find that you still have a syntax error. It is also indicated in that you have two error messages. The problem is that you are missing the semicolon ( ; ) that needs to terminate the MERGE statement. If you try to execute after fixing the GO, you will get the following error:

Msg 10713, Level 15, State 1, Line XXXXX
A MERGE statement must be terminated by a semi-colon (;).

Just add a semicolon before the COMMIT TRANSACTION and the whole thing should work. As in:

values (cell,[date],valueFlag,[value]); -- <-- that guy, right there :-)

Breaking up insert statements with GO

1 Answers1