If you’re getting SQL Server error 10713 that reads “A MERGE statement must be terminated by a semi-colon (;)“, it’s because you’re running a MERGE statement without terminating it with a semi-colon.

The MERGE statement requires a semi-colon at the end of it in order to correctly terminate the statement.

To fix this issue, put a semi-colon at the end of your MERGE statement.

Example of Error

Here’s an example of code that produces the error:

MERGE TargetTable AS target
USING SourceTable AS source
ON target.ID = source.ID

WHEN MATCHED THEN
    UPDATE SET target.Name = source.Name, 
               target.Quantity = source.Quantity

WHEN NOT MATCHED BY TARGET THEN
    INSERT (ID, Name, Quantity) 
    VALUES (source.ID, source.Name, source.Quantity)

WHEN NOT MATCHED BY SOURCE THEN
    DELETE

Output:

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

As expected, an error occurred.

Solution

As mentioned, to fix this issue, place a semi-colon at the end of the MERGE statement:

MERGE TargetTable AS target
USING SourceTable AS source
ON target.ID = source.ID

WHEN MATCHED THEN
    UPDATE SET target.Name = source.Name, 
               target.Quantity = source.Quantity

WHEN NOT MATCHED BY TARGET THEN
    INSERT (ID, Name, Quantity) 
    VALUES (source.ID, source.Name, source.Quantity)

WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

Output:

(3 rows affected)

This time it ran without error.

If you’re using the OUTPUT clause to output the result of the merge, then move the semi-colon to the end of the OUTPUT clause.

Example:

MERGE TargetTable AS target
USING SourceTable AS source
ON target.ID = source.ID

WHEN MATCHED THEN
    UPDATE SET target.Name = source.Name, 
               target.Quantity = source.Quantity

WHEN NOT MATCHED BY TARGET THEN
    INSERT (ID, Name, Quantity) 
    VALUES (source.ID, source.Name, source.Quantity)

WHEN NOT MATCHED BY SOURCE THEN
    DELETE

OUTPUT 
    $action AS Operation,
    DELETED.ID AS OldID, 
    DELETED.Name AS OldName, 
    DELETED.Quantity AS OldQuantity,
    INSERTED.ID AS NewID, 
    INSERTED.Name AS NewName, 
    INSERTED.Quantity AS NewQuantity
    INTO MergeLog;                  -- Semi-colon goes here