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