4

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'.

closed as off-topic by Colin 't Hart, hot2use, Marco, MDCCL, RDFozz May 7 '18 at 16:01

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Colin 't Hart, hot2use, Marco, MDCCL, RDFozz
If this question can be reworded to fit the rules in the help center, please edit the question.

10

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 :-)

Not the answer you're looking for? Browse other questions tagged or ask your own question.