SQL Server › Merge Statement (Upsert)

How do you use the OUTPUT clause together with a MERGE statement to capture which rows were inserted, updated, or deleted?

Intermediate

Answer

You add an OUTPUT clause to your MERGE statement, referencing the special $action column along with the inserted and deleted pseudo tables, which lets you capture exactly what happened to each row, such as logging every insert, update, and delete performed by the MERGE for auditing purposes.

MERGE INTO Products AS target
USING StagingProducts AS source
ON target.ProductId = source.ProductId
WHEN MATCHED THEN UPDATE SET target.Price = source.Price
WHEN NOT MATCHED THEN INSERT (ProductId, Price) VALUES (source.ProductId, source.Price)
OUTPUT $action, inserted.ProductId, inserted.Price;

Real-world example A pricing synchronization job captures a detailed log of exactly which products were updated versus newly inserted during each run, using the OUTPUT clause to feed that information into an audit table for later review.

Common follow-ups: Can the OUTPUT clause from a MERGE statement be inserted directly into another table?;What does the $action column actually contain for each type of operation?

Change Data Capture (CDC);Data Types & Schema Design