PrismitaLEARN · PRACTICE · GROW

What is the MERGE statement, and what are its pitfalls?

SQL Server T-SQL Advanced 3–5 Yrs5+ Yrs SQL Query 1 min read

Quick Answer

MERGE does an upsert in one statement: WHEN MATCHED THEN UPDATE, WHEN NOT MATCHED THEN INSERT, optionally WHEN NOT MATCHED BY SOURCE THEN DELETE. It's concise but has a long history of bugs and concurrency hazards — many teams prefer an explicit UPDATE then INSERT (or the reverse) with proper locking.

MERGE combines insert, update and delete against a target based on whether a source row matches — the classic upsert. It reads nicely, but it has a reputation: there have been real correctness bugs, and by default it's not safe under concurrency — two sessions can both decide to insert and one gets a key violation. If you must use it for a single-row upsert you need HOLDLOCK on the target. Honestly, for OLTP upserts I usually write an explicit update, check @@ROWCOUNT, and insert if it was zero, all in a transaction. MERGE is genuinely good for batch ETL loads from a staging table.

  • Under concurrency MERGE can still hit primary-key violations or deadlocks unless you use HOLDLOCK/SERIALIZABLE on the target.
  • Microsoft has documented multiple MERGE correctness bugs over the years.
  • A safe single-row upsert: UPDATE ... IF @@ROWCOUNT = 0 INSERT ... inside a transaction, or rely on a unique index + INSERT ... WHERE NOT EXISTS.
  • MERGE shines for set-based ETL/staging loads where concurrency isn't a factor.
All SQL Server questions More on T-SQL