SQL Server MERGE Statement: Pitfalls and a Safer Upsert Pattern

"Insert the row if it does not exist, otherwise update it" is one of the most common tasks in any application. SQL Server's MERGE statement looks like the perfect tool, and it can be, but it has sharp edges that cause duplicate key errors, deadlocks and accidental deletes in busy systems. Here is what to watch for, and a simpler pattern that is safe under load.

SQL Server MERGE pitfalls and upsert pattern

How MERGE works

MERGE dbo.Employee AS target
USING (SELECT @EmployeeCode AS EmployeeCode, @FullName AS FullName) AS source
    ON target.EmployeeCode = source.EmployeeCode
WHEN MATCHED THEN
    UPDATE SET FullName = source.FullName
WHEN NOT MATCHED BY TARGET THEN
    INSERT (EmployeeCode, FullName, DepartmentId, CountryCode, HireDate)
    VALUES (source.EmployeeCode, source.FullName, 1, 'IN', CAST(GETDATE() AS DATE));

It compares a source with a target and runs an update, insert or delete for each row, all in one statement.

Pitfall 1: race conditions

A single MERGE statement is not automatically safe against concurrent calls. Under the default isolation level, two sessions can both check for EmployeeCode = 'E100', both find nothing, and both try to insert it. One of them then fails with a primary key or unique index violation, which typically shows up only under real production load.

If you use MERGE for upserts, add a HOLDLOCK hint (serializable semantics) on the target, so the range stays locked between the check and the write:

MERGE dbo.Employee WITH (HOLDLOCK) AS target
USING ...

Pitfall 2: duplicate rows in the source

If the source contains two rows that match the same target row, MERGE fails with an error, because it would update the same row twice. De-duplicate the source first, for example with ROW_NUMBER(), before merging bulk data.

Pitfall 3: WHEN NOT MATCHED BY SOURCE

WHEN NOT MATCHED BY SOURCE THEN DELETE;

This deletes every target row that is not in the source. With a full source table that is exactly what you want for a sync. With a filtered or partial source, such as one department's staging data, it deletes everything else in the target. Always filter the target in that clause, or avoid it unless the source is truly complete.

Pitfall 4: the semicolon

A MERGE statement must end with a semicolon, or you get a syntax error. It is a small thing, but it trips people up when they add MERGE to older code that does not use semicolons.

Pitfall 5: complexity and triggers

Triggers fire once per action type (insert, update, delete) for the whole statement, and the plan for a large MERGE with many clauses can be harder to tune than separate statements. For complex batch syncs, separate UPDATE, INSERT and DELETE statements are often easier to read, tune and debug.

A safer single-row upsert pattern

For single-row upserts from an application, this pattern is simple, readable and safe under concurrency:

SET XACT_ABORT ON;
BEGIN TRANSACTION;

UPDATE dbo.Employee WITH (UPDLOCK, SERIALIZABLE)
SET    FullName = @FullName
WHERE  EmployeeCode = @EmployeeCode;

IF @@ROWCOUNT = 0
BEGIN
    INSERT INTO dbo.Employee (EmployeeCode, FullName, DepartmentId, CountryCode, HireDate)
    VALUES (@EmployeeCode, @FullName, @DepartmentId, @CountryCode, @HireDate);
END

COMMIT TRANSACTION;
  • The UPDLOCK, SERIALIZABLE hints lock the key range even when the row does not exist yet, so a second session waits instead of inserting a duplicate.
  • When the row exists (the common case in many systems), only one statement runs.
  • It needs an index on EmployeeCode so the lock covers a small range, not the whole table.

When MERGE is a good choice

  • Batch synchronisation from a complete staging table, run by one job at a time.
  • ETL processes where its single-statement form makes the logic clearer.
  • When you also want the OUTPUT $action clause to log what happened to each row.

Quick checklist

  • Using MERGE for concurrent upserts? Add WITH (HOLDLOCK) on the target.
  • De-duplicate the source first.
  • Be very careful with WHEN NOT MATCHED BY SOURCE THEN DELETE.
  • End the statement with a semicolon.
  • For single-row upserts, prefer UPDATE-then-INSERT with proper locking.

Related reading:

Comments

Post a Comment

Popular posts from this blog

How to Read a SQL Server Execution Plan (Beginner's Guide)

Image
When a query is slow, the execution plan tells you exactly what SQL Server did to run it: which indexes it used, how it joined tables and where the time went. Plans can look intimidating, but you only need a few habits to find the problem in most of them. Estimated vs actual plans SQL Server Management Studio (SSMS) can show two kinds of plan: Estimated plan (Ctrl+L): what SQL Server plans to do, without running the query. Useful for queries that are too slow or too risky to run. Actual plan (Ctrl+M, then run the query): the same plan plus what really happened, including actual row counts and warnings. Use this whenever you can. Turn on IO and time statistics at the same time so you have numbers to compare after each change: SET STATISTICS IO, TIME ON; -- run the query with "Include Actual Execution Plan" enabled SET STATISTICS IO, TIME OFF; Read from right to left Data flows from right to left. The operators on the far right read data from tables and indexes; each operato...
Read more

SARGable Queries in SQL Server: Why Functions in WHERE Slow You Down

Image
You add the perfect index, run the query, and SQL Server still scans the whole table. One of the most common reasons is a non-SARGable predicate: a WHERE clause written in a way that stops SQL Server from using the index to seek. The fix is usually a small rewrite. What SARGable means SARGable stands for S earch ARG ument- able . A predicate is SARGable when SQL Server can use it to jump straight to the matching rows in an index (an index seek). If you wrap the indexed column in a function or calculation, SQL Server can no longer search the index for a value; it has to calculate the expression for every row, which means a scan. The rule of thumb: leave the column bare on one side of the comparison , and do any calculation on the other side. Common non-SARGable patterns and their fixes Functions on date columns -- Not SARGable: YEAR() runs on every row WHERE YEAR(OrderDate) = 2026 -- SARGable: a range on the bare column WHERE OrderDate >= '2026-01-01' AND OrderDate < ...
Read more

SQL Server Statistics Explained: Why Your Query Plans Go Wrong

Image
When a query suddenly picks a terrible plan, the root cause is very often statistics . SQL Server's optimizer does not look at your data when it builds a plan; it looks at statistics about your data. If those are missing or out of date, it guesses wrong, and a wrong guess can mean a scan instead of a seek, the wrong join type or a memory spill. What statistics are A statistics object describes how values are distributed in one or more columns. SQL Server creates one automatically for every index, and, with AUTO_CREATE_STATISTICS on, for columns you filter or join on. The optimizer uses them to estimate how many rows each step will return, and those estimates drive almost every plan choice. How to read a statistics object DBCC SHOW_STATISTICS ('dbo.PayrollLine', 'IX_PayrollLine_Run_PayCode'); The output has three parts: Header: when the statistics were last updated, how many rows the table had, and how many rows were sampled . Density vector: how unique the column...
Read more