MERGE, upserts and the ways they go wrong

MERGE compares a source with a target on a join condition and applies a different action to each outcome, in one statement:

MERGE stock AS t
USING feed AS s
   ON t.sku = s.sku
WHEN MATCHED AND t.qty <> s.qty THEN
  UPDATE SET qty = s.qty
WHEN NOT MATCHED BY TARGET THEN
  INSERT (sku, qty) VALUES (s.sku, s.qty)
WHEN NOT MATCHED BY SOURCE THEN
  DELETE
OUTPUT $action, inserted.sku, deleted.sku;

• WHEN MATCHED — the row exists on both sides.
• WHEN NOT MATCHED [BY TARGET] — only in the source: insert it.
• WHEN NOT MATCHED BY SOURCE — only in the target.
• OUTPUT $action reports INSERT, UPDATE or DELETE per row.

A MERGE statement must end with a semicolon.

1CREATE TABLE stock (sku NVARCHAR(10) PRIMARY KEY, qty INT NOT NULL);2CREATE TABLE feed (sku NVARCHAR(10) PRIMARY KEY, qty INT NOT NULL);3INSERT INTO stock VALUES (N'A', 5), (N'B', 0), (N'C', 9);4INSERT INTO feed VALUES (N'A', 7), (N'C', 9), (N'D', 3);

1DECLARE @changes TABLE (action NVARCHAR(10), sku NVARCHAR(10));2 3MERGE stock AS t4USING feed AS s5   ON t.sku = s.sku6WHEN MATCHED AND t.qty <> s.qty THEN7  UPDATE SET qty = s.qty8WHEN NOT MATCHED BY TARGET THEN9  INSERT (sku, qty) VALUES (s.sku, s.qty)10WHEN NOT MATCHED BY SOURCE THEN11  DELETE12OUTPUT $action, COALESCE(inserted.sku, deleted.sku) INTO @changes;13 14SELECT c.action, c.sku, st.qty AS qty_now15FROM @changes AS c16LEFT JOIN stock AS st ON st.sku = c.sku17ORDER BY c.sku;

Pitfall 1: duplicates in the source

If two source rows match the same target row, the target row would be updated twice, and SQL Server refuses the whole statement with error 8672. A feed that may contain the same key twice must be reduced to one row per key first, for example with ROW_NUMBER() keeping the latest.

1CREATE TABLE prices (sku NVARCHAR(10) PRIMARY KEY, price INT NOT NULL);2CREATE TABLE feed (seq INT PRIMARY KEY, sku NVARCHAR(10) NOT NULL, price INT NOT NULL);3INSERT INTO prices VALUES (N'A', 10);4INSERT INTO feed VALUES (1, N'A', 11), (2, N'A', 12);

1DECLARE @log TABLE (id INT, attempt NVARCHAR(30), outcome NVARCHAR(40));2 3BEGIN TRY4  MERGE prices AS t5  USING feed AS s ON t.sku = s.sku6  WHEN MATCHED THEN UPDATE SET price = s.price;7  INSERT INTO @log VALUES (1, N'raw feed', N'ok');8END TRY9BEGIN CATCH10  INSERT INTO @log VALUES (1, N'raw feed', CONCAT(N'error ', ERROR_NUMBER()));11END CATCH;12 13WITH latest AS (14  SELECT sku, price, ROW_NUMBER() OVER (PARTITION BY sku ORDER BY seq DESC) AS rn15  FROM feed16)17MERGE prices AS t18USING (SELECT sku, price FROM latest WHERE rn = 1) AS s ON t.sku = s.sku19WHEN MATCHED THEN UPDATE SET price = s.price;20INSERT INTO @log VALUES (2, N'latest row per sku', CONCAT(N'ok, price now ', (SELECT price FROM prices WHERE sku = N'A')));21 22SELECT attempt, outcome FROM @log ORDER BY id;

Pitfall 2: NOT MATCHED BY SOURCE means the whole target

WHEN NOT MATCHED BY SOURCE THEN DELETE deletes every target row that has no partner in the source. If the source is a partial feed — one warehouse, one category, today's changes — every other row in the target qualifies. Restrict the target first (a CTE over the target that filters to the same scope) or add a condition to the clause.

1CREATE TABLE stock (warehouse NVARCHAR(5) NOT NULL, sku NVARCHAR(10) NOT NULL, qty INT NOT NULL, PRIMARY KEY (warehouse, sku));2CREATE TABLE tbs_feed (sku NVARCHAR(10) PRIMARY KEY, qty INT NOT NULL);3INSERT INTO stock VALUES (N'TBS', N'A', 1), (N'TBS', N'B', 2), (N'BUS', N'A', 7), (N'BUS', N'C', 8);4INSERT INTO tbs_feed VALUES (N'A', 4);

1SELECT warehouse, sku, qty INTO #naive FROM stock;2SELECT warehouse, sku, qty INTO #scoped FROM stock;3 4MERGE #naive AS t5USING (SELECT N'TBS' AS warehouse, sku, qty FROM tbs_feed) AS s6   ON t.warehouse = s.warehouse AND t.sku = s.sku7WHEN MATCHED THEN UPDATE SET qty = s.qty8WHEN NOT MATCHED BY SOURCE THEN DELETE;9 10WITH tbs AS (SELECT * FROM #scoped WHERE warehouse = N'TBS')11MERGE tbs AS t12USING tbs_feed AS s13   ON t.sku = s.sku14WHEN MATCHED THEN UPDATE SET qty = s.qty15WHEN NOT MATCHED BY SOURCE THEN DELETE;16 17SELECT 'naive' AS version, warehouse, sku, qty FROM #naive18UNION ALL19SELECT 'scoped', warehouse, sku, qty FROM #scoped20ORDER BY version, warehouse, sku;

Pitfall 3: concurrency

MERGE is one statement, but it is not protected against another session inserting the same key between its check and its insert. Under the default isolation level two sessions can both find the key missing and both try to insert it; one fails with a primary key violation. The usual fix is MERGE target WITH (HOLDLOCK), which applies serializable range locking to the target for the statement. The same applies to the two-statement upsert UPDATE ...; IF @@ROWCOUNT = 0 INSERT ...;, which needs WITH (UPDLOCK, HOLDLOCK) inside a transaction.

Many teams prefer separate UPDATE and INSERT statements to MERGE: they are easier to read and to tune one at a time, and MERGE has had its share of product bugs over the years. Whichever you choose, handle the three pitfalls above.

Common mistakes

• Merging a raw feed with duplicate keys. The second example fails with error 8672.
• A partial source with NOT MATCHED BY SOURCE THEN DELETE. The third example loses the other warehouse's stock.
• Upserting without HOLDLOCK and then seeing rare duplicate-key errors under load.
• Forgetting the semicolon after MERGE.

What interviewers ask

• Acts on target rows with no source row; dangerous with a partial source.