SpDev Install free

The MERGE statement in SQL Server: syntax, pitfalls and safer alternatives

How to write an upsert with MERGE in SQL Server, the errors you'll meet, why it needs HOLDLOCK under concurrency, and when separate UPDATE and INSERT statements are the better choice.

By Areeb Ahmer, . 5 minute read.

MERGE compares a source (a table, a query, a list of values) with a target table, and in one statement updates the rows that match, inserts the ones that don’t, and optionally deletes target rows the source no longer has. It’s the standard SQL way to write an upsert or synchronize a table.

It’s also one of the statements experienced SQL Server developers are most wary of. This article shows how to write it well, and when not to. The examples run against the SpDevDemo database.

An upsert with MERGE

Keep a price list in sync with incoming prices:

CREATE TABLE dbo.PriceList (ProductId INT PRIMARY KEY, Price DECIMAL(10, 2) NOT NULL, UpdatedAt DATETIME2 NOT NULL);
INSERT dbo.PriceList (ProductId, Price, UpdatedAt)
SELECT ProductId, ListPrice, '2026-01-01' FROM dbo.Products WHERE ProductId <= 3;
GO

MERGE dbo.PriceList WITH (HOLDLOCK) AS t
USING (VALUES (1, 19.99), (2, 42.00), (4, 7.50)) AS s (ProductId, Price)
    ON t.ProductId = s.ProductId
WHEN MATCHED AND t.Price <> s.Price THEN
    UPDATE SET Price = s.Price, UpdatedAt = SYSDATETIME()
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductId, Price, UpdatedAt) VALUES (s.ProductId, s.Price, SYSDATETIME())
OUTPUT $action AS Action, inserted.ProductId, deleted.Price AS OldPrice, inserted.Price AS NewPrice;
Action  ProductId  OldPrice  NewPrice
------  ---------  --------  --------
UPDATE  1          42.00     19.99
UPDATE  2          79.00     42.00
INSERT  4          NULL      7.50

The parts:

  • MERGE target USING source ON condition: like a join between the two.
  • WHEN MATCHED [AND extra condition] THEN UPDATE (or DELETE): for target rows that have a match. The extra condition avoids updating rows that haven’t changed.
  • WHEN NOT MATCHED [BY TARGET] THEN INSERT: for source rows with no match.
  • WHEN NOT MATCHED BY SOURCE THEN DELETE (or UPDATE): for target rows the source doesn’t contain. Use it to make the target a mirror of the source, and be careful: with a partial source, it deletes everything else.
  • OUTPUT $action tells you what happened to each row: INSERT, UPDATE or DELETE.
  • The statement must end with a semicolon, unlike most T-SQL.

Errors you’ll meet

The missing semicolon:

CREATE TABLE #t (Id INT PRIMARY KEY, v INT);
MERGE #t AS t USING (VALUES (1, 10)) AS s (Id, v) ON t.Id = s.Id
WHEN NOT MATCHED THEN INSERT (Id, v) VALUES (s.Id, s.v)
Msg 10713, Level 15, State 1
A MERGE statement must be terminated by a semi-colon (;).

Duplicate keys in the source:

CREATE TABLE #target (Id INT PRIMARY KEY, v INT);
INSERT #target VALUES (1, 0);

MERGE #target AS t
USING (VALUES (1, 10), (1, 20)) AS s (Id, v)
    ON t.Id = s.Id
WHEN MATCHED THEN UPDATE SET v = s.v;
Msg 8672, Level 16, State 1
The MERGE statement attempted to UPDATE or DELETE the same row more than once.
This happens when a target row matches more than one source row. ...

The source must have at most one row per target key. Deduplicate it first (with ROW_NUMBER() or GROUP BY) and decide which duplicate wins.

MERGE and concurrency: HOLDLOCK

A MERGE checks whether the row exists, then inserts it if it doesn’t. Without extra locking, two sessions upserting the same new key at the same time can both see “not there”, both insert, and one fails with a primary key violation. That’s why the example above has WITH (HOLDLOCK) on the target: it holds a range lock from the check to the insert, so the second session waits instead of failing.

This isn’t specific to MERGE: an IF EXISTS ... UPDATE ... ELSE INSERT has the same race, and the same fix.

Why people avoid MERGE

MERGE has had a long list of reported bugs in SQL Server over the years, especially in combination with triggers, filtered indexes, indexed views, foreign keys and the OUTPUT clause. Many were fixed in later builds; the reputation stuck, and some teams ban it outright.

Practical guidance:

  • Fine: loading staging data into a table in a batch job, especially when you want OUTPUT $action for an audit.
  • Think twice: single-row upserts called thousands of times a second (two simple statements are easier to reason about and index for), and targets with triggers or indexed views.
  • Always: HOLDLOCK under concurrency, a deduplicated source, a semicolon, and tests of each branch.

The alternative: UPDATE, then INSERT

For a single-row upsert:

DECLARE @ProductId INT = 5, @Price DECIMAL(10, 2) = 12.00;

SET XACT_ABORT ON;
BEGIN TRANSACTION;

UPDATE dbo.PriceList WITH (UPDLOCK, SERIALIZABLE)
SET Price = @Price, UpdatedAt = SYSDATETIME()
WHERE ProductId = @ProductId;

IF @@ROWCOUNT = 0
    INSERT dbo.PriceList (ProductId, Price, UpdatedAt) VALUES (@ProductId, @Price, SYSDATETIME());

COMMIT;

SELECT ProductId, Price FROM dbo.PriceList WHERE ProductId = @ProductId;

The UPDLOCK, SERIALIZABLE hints on the UPDATE lock the key range even when the row doesn’t exist yet, so a concurrent caller waits until this transaction commits. Try the UPDATE first when most calls update existing rows; it saves the existence check.

For a set of rows, the same idea:

CREATE TABLE #incoming (ProductId INT PRIMARY KEY, Price DECIMAL(10, 2));
INSERT #incoming VALUES (1, 21.00), (6, 9.99);

BEGIN TRANSACTION;

UPDATE p SET Price = i.Price, UpdatedAt = SYSDATETIME()
FROM dbo.PriceList AS p WITH (UPDLOCK, SERIALIZABLE)
JOIN #incoming AS i ON i.ProductId = p.ProductId;

INSERT dbo.PriceList (ProductId, Price, UpdatedAt)
SELECT i.ProductId, i.Price, SYSDATETIME()
FROM #incoming AS i
WHERE NOT EXISTS (SELECT 1 FROM dbo.PriceList AS p WITH (UPDLOCK, SERIALIZABLE) WHERE p.ProductId = i.ProductId);

COMMIT;

SELECT ProductId, Price FROM dbo.PriceList ORDER BY ProductId;
ProductId  Price
---------  ------
1          21.00
2          42.00
3          116.00
4          7.50
5          12.00
6          9.99

Two statements instead of one, each with an obvious plan.

Testing a MERGE

MERGE packs several code paths into one statement, so test each one: a new row, a changed row, an unchanged row, a duplicate in the source, and, if you use it, a row that should be deleted. SpDev’s sandbox mode runs a procedure inside a transaction that is rolled back at the end, so you can run it against the same development data again and again; its coverage view also shows which branches of the surrounding procedure your test runs reached.