SQL Server › Merge Statement (Upsert)

What are the three main clauses of a MERGE statement, and what does each one control?

Beginner

Answer

WHEN MATCHED handles rows that exist in both the target and source, typically used for updates, WHEN NOT MATCHED BY TARGET handles rows that exist in the source but not the target, typically used for inserts, and WHEN NOT MATCHED BY SOURCE handles rows that exist in the target but not the source, often used to delete rows that are no longer present in the incoming data.

MERGE INTO Inventory AS target
USING NewInventory AS source
ON target.ItemId = source.ItemId
WHEN MATCHED THEN UPDATE SET target.Quantity = source.Quantity
WHEN NOT MATCHED BY TARGET THEN INSERT (ItemId, Quantity) VALUES (source.ItemId, source.Quantity)
WHEN NOT MATCHED BY SOURCE THEN DELETE;

Real-world example An inventory synchronization process updates existing item quantities, adds any brand new items, and removes items that no longer appear in the latest inventory feed, all handled cleanly through the three clauses of a single MERGE statement.

Common follow-ups: Is it mandatory to include all three WHEN clauses in every MERGE statement?;What happens if a row matches multiple source rows unexpectedly?

Data Types & Schema Design;Constraints (Primary Key Foreign Key Check & Unique)