SQL Error 10713 a MERGE Statement Must Be Terminated by a Semi Colon

Sorry for not writing any blog posts for a week, I’ve been writing on a novel and I’ve gotten into a good flow and have been spending that precious free hour each night when the kid sleeps to write. However, after some discussions with a friend I realized I had forgotten to post about the MERGE statement error when using Heidi. Heidi supports MS SQL but doesn’t guarantee that all queries will work. Unfortunately, it won’t tell what doesn’t work, nor give a helpful error message. If you try to do a MERGE, regardless of how you write it, it will say that you are missing a semi colon. Even if it’s there.

The query:

MERGE INTO AggregatedData WITH (HOLDLOCK)  AS Target
USING (
	VALUES 
	('1','1')
	) as s(DeviceId,IsAccumulated)
ON Target.DeviceId = s.DeviceId
WHEN MATCHED THEN
 UPDATE SET Target.IsAccumulated = s.IsAccumulated
;

SQL Error 10713 a MERGE Statement Must Be Terminated by a Semi Colon
The error message:

SQL Error (10713): A MERGE statement must be terminated by a semi-colon (;)

The message is misleading, you can add as many semi colons as you want and get the same error (there should only be one, by the way). When I got the error, I tried installing SSMS, but my computer was acting up, and I only had to test one query that used MERGE. Therefore, I used the Invoke-SqlCmd cmdlet to run the query and had no problems. The PowerShell script is further down in this post.

To be able to run the query in Heidi you need to run the MERGE query as one batch instead of the default option, running the queries one by one. Next to the Run icon there is a tiny dropdown where you can select the batch option. This might fix many of you problems, for example when you are declaring variables.

SQL Error 10713 a MERGE Statement Must Be Terminated by a Semi Colon

$query = 
@"
MERGE INTO AggregatedData WITH (HOLDLOCK)  AS Target
USING (VALUES ('1','1')) as s(DeviceId,IsAccumulated)
ON Target.DeviceId = s.DeviceId
WHEN MATCHED THEN
 UPDATE SET Target.IsAccumulated = s.IsAccumulated
;
"@

Invoke-Sqlcmd -Query $query

SQL Error 10713 a MERGE Statement Must Be Terminated by a Semi Colon

Comments