PrismitaLEARN · PRACTICE · GROW

What is the MERGE statement (upsert) in SQL Server?

SQL Server T-SQL Intermediate 2–3 Yrs3–5 Yrs SQL Query 1 min read

Quick Answer

MERGE combines INSERT, UPDATE and DELETE into one statement based on whether rows from a source match a target: WHEN MATCHED THEN UPDATE, WHEN NOT MATCHED THEN INSERT, WHEN NOT MATCHED BY SOURCE THEN DELETE. Useful for synchronising a table from a source set, but it has known concurrency and bug caveats — many teams prefer a plain UPDATE then INSERT.

MERGE lets you do insert, update and delete in one statement by comparing a source set to a target: when a row matches you update it, when it doesn't exist you insert, and optionally when it's in the target but not the source you delete. It's handy for syncing a table from staged data. That said, I'm cautious with it — it's had real bugs over the years, and under concurrency it can deadlock or hit unique-constraint violations unless you add a HOLDLOCK hint. For a simple upsert I often just do an UPDATE, check @@ROWCOUNT, and INSERT if it was zero.

  • Classic use: load a staging table then MERGE it into the target to apply inserts and updates in one pass.
  • Caveats: historically several Microsoft-acknowledged bugs; under concurrency it can deadlock or violate unique constraints unless you add HOLDLOCK/SERIALIZABLE hints on the target.
  • Simple alternative for a single-row upsert: UPDATE ...; IF @@ROWCOUNT = 0 INSERT ...; inside a transaction with the right isolation.
  • Always specify all the WHEN clauses you actually want — an unqualified MERGE can delete more than expected.
All SQL Server questions More on T-SQL