MERGE Quick Reference (T-SQL)

The T-SQL MERGE statement allows ‘upsert’ functionality (UPDATE or INSERT) in one statement. It's usage comes with some caveats, documented in Aaron Bertrand's post Use Caution with SQL Server's MERGE Statement. The syntax is a little difficult to remember, so I am keeping a quick post here for reference.

MERGE INTO
  SchemaName.TableName WITH (HOLDLOCK) AS TGT
USING
  (VALUES
  (1, 'aaa'),
  2, 'bbb'),
  3, 'ccc'),
  4, 'ddd')
  ) AS SRC(Pk, ColB)
  ON (SRC.Pk = TGT.Pk)

WHEN NOT MATCHED BY TARGET THEN
  INSERT (Pk, ColB)
  VALUES (SRC.Pk, SRC.ColB)

WHEN MATCHED AND EXISTS (SELECT SRC.ColB EXCEPT SELECT TGT.ColB) THEN /* Don't update if the values are the same */
  UPDATE SET
  ColB = SRC.ColB

WHEN NOT MATCHED BY SOURCE THEN DELETE /* Use with caution, but will keep the target table in sync with the source */
;

27 Dec 2017 Permalink data