One statement promises to update, insert, and delete every row in the right place. MERGE statement pitfalls begin when source grain and concurrent writers disagree with that promise.
Avoid MERGE Statement Pitfalls With One Source Row per Key
MERGE needs a clear match between source and target. If two source rows match one target row for an update, SQL Server can reject the operation because that row would be updated more than once. Validate source key uniqueness before the statement. A staging table with a unique constraint can enforce the rule.
I inspect the source grain first. One row per customer and one row per customer address are different sets. A join upstream can multiply rows even when the source file had unique keys. Do not ask MERGE to choose a winner from ambiguous input.
What should happen when duplicates exist? Reject the batch, choose a documented latest version, or combine values under a business rule. Decide before writing the target.
SELECT CustomerId, COUNT_BIG(*) AS SourceRows
FROM dbo.StageCustomer
GROUP BY CustomerId
HAVING COUNT_BIG(*) > 1;Keep ON for Identity
The ON clause should state how source and target rows match, usually a stable business key. Among MERGE statement pitfalls, adding action filters to ON can turn an existing row into a not-matched row. That can cause a duplicate insert. Put eligibility in the appropriate WHEN clause instead.
I test matched, new, missing, and unchanged keys. A small matrix makes action rules visible. If the statement deletes target rows missing from the source, confirm that the source is a complete snapshot. A partial extract is not a delete list.
Keep target unique constraints in place. They are the final defense when a source key or concurrency rule is wrong. An upsert that works only after disabling uniqueness has no trustworthy identity model.
SELECT s.CustomerId
FROM dbo.StageCustomer AS s
LEFT JOIN dbo.Customer AS t ON t.CustomerId = s.CustomerId
WHERE t.CustomerId IS NULL;Plan for MERGE Statement Pitfalls With Concurrent Writers
Two sessions can both observe a missing key before either inserts it. MERGE does not automatically remove every race. Use a unique constraint and an isolation strategy suited to the workload. A HOLDLOCK target hint is one technique to evaluate, with its blocking cost included.
I run concurrency tests when two jobs or applications can touch the same target. A single-session rehearsal cannot expose the race. Verify final data and error handling, not merely that each statement finished.
If heavy concurrency is normal, separate UPDATE and INSERT statements can be easier to reason about and tune. They still need transaction and uniqueness rules. Simpler syntax alone does not solve concurrent identity.
MERGE dbo.Customer WITH (HOLDLOCK) AS t
USING dbo.StageCustomer AS s
ON t.CustomerId = s.CustomerId
WHEN MATCHED THEN
UPDATE SET t.CustomerName = s.CustomerName
WHEN NOT MATCHED BY TARGET THEN
INSERT (CustomerId, CustomerName)
VALUES (s.CustomerId, s.CustomerName);
Account for Trigger Behavior
MERGE can perform several action types in one statement. Target triggers still fire, and their inserted and deleted tables can contain sets of rows. Trigger code that assumes one row can fail or write bad audit records. Review it before changing a load to MERGE.
Inside an AFTER trigger, @@ROWCOUNT reflects total rows affected by the MERGE, not just one action type. Use inserted and deleted sets and explicit grouping. Test all action combinations the statement can produce.
I keep trigger side effects in the load review: audit writes, notifications, and downstream queue work. A shorter target statement can create a more complex trigger path. The total operation includes those effects.
SELECT name, is_disabled
FROM sys.triggers
WHERE parent_id = OBJECT_ID(N'dbo.Customer')
ORDER BY name;Audit Every Action
OUTPUT can report whether each target row was inserted, updated, or deleted. Capture keys and actions with the load RunId when reconciliation needs them. Do not assume action counts equal source row counts if the statement filters branches.
I compare action totals with staged and rejected counts. Each difference should have an explanation. A green MERGE statement with an unexpected delete count is one of the quieter pitfalls and a reason to stop publication. A full snapshot load deserves particular caution around the not-matched-by-source branch.
Store only audit columns you need. OUTPUT can include sensitive data, so access and retention rules apply. A run log with actions and keys is more useful than a giant copy of every target row.
SELECT RunId, MergeAction, COUNT_BIG(*) AS ActionRows
FROM dbo.MergeAudit
GROUP BY RunId, MergeAction;Use Two Statements When Clearer
An UPDATE joined to stage followed by an INSERT of missing keys is easy to read. Wrap them in a transaction, keep a unique target key, and decide isolation. The order matters if concurrent writers can add keys between statements.
I choose this form when the load has only update and insert behavior and the MERGE plan or trigger path is hard to explain. It does not automatically run faster. It makes action boundaries clearer and gives each statement its own metrics.
Test a retry after partial failure. If the transaction rolled back, both actions repeat. If a commit happened but the caller lost confirmation, the second run should update existing rows and avoid duplicates. Idempotency still matters.
BEGIN TRANSACTION;
UPDATE t
SET CustomerName = s.CustomerName
FROM dbo.Customer AS t
JOIN dbo.StageCustomer AS s ON s.CustomerId = t.CustomerId;
INSERT dbo.Customer (CustomerId, CustomerName)
SELECT s.CustomerId, s.CustomerName
FROM dbo.StageCustomer AS s
WHERE NOT EXISTS
(
SELECT 1 FROM dbo.Customer AS t WITH (UPDLOCK, HOLDLOCK)
WHERE t.CustomerId = s.CustomerId
);
COMMIT TRANSACTION;Test the Exact Workload for MERGE Statement Pitfalls
Build cases for duplicate source keys, NULLs, unchanged rows, concurrent inserts, triggers, and partial extracts. Inspect the final target and action log. A query that compiles is not proof that every branch behaves as intended.
Measure reads, CPU, duration, blocking, and log use on representative data. One statement can have a complex plan. Two statements can have simpler plans but more total work. Choose from results on your server and the need for clear recovery.
MERGE is useful when source and action rules are precise. Treat it as a multi-action operation with concurrency and trigger consequences. If the work is simpler as separate statements, SQL Server will not complain about your restraint.
What does the target do when two source rows match one key? A merge plan needs an explicit rule for duplicates before any write starts. I check source uniqueness with a GROUP BY and reject ambiguous rows rather than letting statement behavior choose the outcome. Concurrency matters too. Another session can insert the same key between the match check and the write unless locking and a unique constraint protect the target. Test with two concurrent sessions.
When a source feed is incomplete, omit a delete branch entirely. I require a separate signal that the feed is a complete snapshot before removing target rows. That signal belongs in the load contract and the run log.
Related reading on this blog: FIX : Error Msg 8672: The MERGE Statement Attempted to UPDATE or DELETE the Same Row More Than Once and Merge Operations: Insert, Update, Delete in Single Execution.
MERGE is not a substitute for source keys and concurrency rules, it is one way to apply them.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
