Upsert in SQL Server

SQL Server has no INSERT ... ON CONFLICT (PostgreSQL) or INSERT ... ON DUPLICATE KEY UPDATE (MySQL). The dialect gives you two ways to "update if exists, insert if not": the MERGE statement, and the manual UPDATE-then-INSERT pattern. Both work. Both have sharp edges that are genuinely worth understanding before you pick one, because the sharp edges are concurrency bugs -- the kind that pass every test and fail in production.

MERGE Basics

MERGE compares a source rowset against a target table and applies inserts, updates, and deletes in one statement:

MERGE dbo.customers AS tgt
USING dbo.customers_staging AS src
    ON tgt.customer_id = src.customer_id
WHEN MATCHED THEN
    UPDATE SET tgt.name = src.name,
               tgt.email = src.email,
               tgt.updated_at = SYSUTCDATETIME()
WHEN NOT MATCHED BY TARGET THEN
    INSERT (customer_id, name, email)
    VALUES (src.customer_id, src.name, src.email);

Notes on the syntax:

• MERGE must be terminated with a semicolon. This is one of the few places T-SQL enforces it.
• WHEN NOT MATCHED BY TARGET (source row has no target match) triggers the INSERT. The BY TARGET is optional; WHEN NOT MATCHED means the same thing.
• WHEN NOT MATCHED BY SOURCE (target row has no source match) is the third clause -- it can UPDATE or DELETE target rows absent from the source. This is what makes MERGE a full table-synchronization tool, not just an upsert.
• Each WHEN clause can take an extra predicate: WHEN MATCHED AND src.email <> tgt.email THEN UPDATE ... skips no-op updates.

The Single-Row Upsert

For the common "upsert one row by key" case:

MERGE dbo.settings WITH (HOLDLOCK) AS tgt
USING (SELECT @user_id AS user_id, @key AS [key], @value AS value) AS src
    ON tgt.user_id = src.user_id AND tgt.[key] = src.[key]
WHEN MATCHED THEN
    UPDATE SET tgt.value = src.value
WHEN NOT MATCHED THEN
    INSERT (user_id, [key], value) VALUES (src.user_id, src.[key], src.value);

That WITH (HOLDLOCK) is not decoration. It is the point of the next section.

The Race Condition Nobody Expects

A widespread belief is that MERGE, being a single statement, is atomic and therefore immune to races. It is atomic (it either fully happens or fully doesn't), but atomicity is not isolation. Under the default READ COMMITTED level, two concurrent MERGEs can both check for a key, both find nothing, and both proceed to insert -- one gets a primary key violation. The read phase and the write phase are not one indivisible step unless you make them one.

The fix is the HOLDLOCK (equivalently SERIALIZABLE) hint shown above: it takes a key-range lock during the match check, so the second MERGE waits instead of racing. Every single-row upsert MERGE should carry WITH (HOLDLOCK). The same reasoning applies to the manual pattern below.

The Manual Pattern: UPDATE Then INSERT

The pre-MERGE idiom, still the preference of a large share of SQL Server practitioners:

SET XACT_ABORT ON;
BEGIN TRAN;
 
UPDATE dbo.settings WITH (UPDLOCK, SERIALIZABLE)
SET value = @value
WHERE user_id = @user_id AND [key] = @key;
 
IF @@ROWCOUNT = 0
    INSERT INTO dbo.settings (user_id, [key], value)
    VALUES (@user_id, @key, @value);
 
COMMIT;

Why this shape:

• UPDATE first, not existence-check first. If the row usually exists, this is one statement in the common path, and @@ROWCOUNT tells you whether to insert. A SELECT + IF EXISTS + UPDATE/INSERT sequence is three statements and a wider race window.
• UPDLOCK, SERIALIZABLE on the UPDATE does the same job HOLDLOCK does for MERGE: the key range is locked from the check through the insert, so concurrent callers serialize instead of colliding.
• The explicit transaction is required -- without it, the UPDATE and INSERT are separate autocommit transactions and the locks don't span them. See our transactions guide for why XACT_ABORT ON belongs there.

Should You Use MERGE at All?

You'll find blanket "never use MERGE" advice all over the internet, mostly tracing back to Aaron Bertrand's 2013 bug compilation. The honest current state (as of July 2026) is more nuanced. Hugo Kornelis re-verified the whole list against modern builds in 2023 and found most items fixed, unreproducible, or not actually MERGE bugs -- but a couple of real ones remain. His revised advice, which we endorse:

• Avoid the DELETE action in MERGE (the WHEN NOT MATCHED BY SOURCE THEN DELETE sync pattern is where the remaining serious bugs live). Use a separate DELETE statement instead.
• Be careful when the target is a temporal table.
• Plain insert/update upserts with HOLDLOCK are fine.

Two more practical MERGE caveats:

• Triggers fire once per action type, and @@ROWCOUNT inside a trigger reflects the total affected rows for that action -- test triggers on MERGE targets explicitly.
• Performance is not automatically better than separate UPDATE + INSERT. For large batch loads, a MERGE and a well-indexed UPDATE-then-INSERT pair are usually comparable; measure, don't assume. Index the join key on both sides either way.

OUTPUT: Seeing What Happened

MERGE has one genuinely unique feature: $action in the OUTPUT clause tells you what happened to each row:

MERGE dbo.customers AS tgt
USING dbo.customers_staging AS src ON tgt.customer_id = src.customer_id
WHEN MATCHED THEN UPDATE SET tgt.name = src.name
WHEN NOT MATCHED THEN INSERT (customer_id, name) VALUES (src.customer_id, src.name)
OUTPUT $action, inserted.customer_id;

$action returns 'INSERT', 'UPDATE', or 'DELETE' per row -- useful for audit logs and load reports, and not available in any other T-SQL statement.

Decision Guide

Common Mistakes

Writing MERGE statements by hand is fiddly -- three clauses, aliased columns, and a hint to remember. Mako's AI autocomplete generates the full skeleton from your table schemas, which removes most of the transcription errors.

Mako connects to SQL Server with AI-powered autocomplete. Try it free at mako.ai.