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 */
;

MERGE Quick Reference (T-SQL)