SQL for .NET developers Intermediate

MERGE and upserts

An upsert updates a row if it exists and inserts it if it does not. This lesson shows the simple UPDATE-then-INSERT pattern with the locking hints that make it safe under load, the MERGE statement for syncing a whole table, and the traps that make many teams avoid MERGE.

Safe upsert for one row
  1. StartBegin transactionEverything below is one unit
  2. Step 2UPDATE with UPDLOCK, HOLDLOCKLocks the key, even if the row does not exist yet
  3. Step 3Row changed?Yes: done. No: the row is new
  4. Step 4INSERTOther callers wait for the lock, so no duplicates
  5. EndCommitLocks are released

The problem

Imports and sync jobs often receive a row that may or may not already exist. Checking first with IF EXISTS and then inserting looks right, but two requests at the same moment can both see "not there" and both insert, giving duplicates or a key error.

Safe upsert for one row

Try the UPDATE first. If no row changed, INSERT. The UPDLOCK and HOLDLOCK hints lock the key range inside the transaction, so a second caller waits instead of racing.

SQL
CREATE OR ALTER PROCEDURE dbo.UpsertProduct
    @Sku varchar(20), @Name nvarchar(200), @Price decimal(18,2)
AS
SET NOCOUNT, XACT_ABORT ON;
BEGIN TRAN;
    UPDATE dbo.Products WITH (UPDLOCK, HOLDLOCK)
    SET Name = @Name, Price = @Price
    WHERE Sku = @Sku;

    IF @@ROWCOUNT = 0
        INSERT dbo.Products (Sku, Name, Price) VALUES (@Sku, @Name, @Price);
COMMIT;

MERGE for syncing a whole table

MERGE compares a source with a target and inserts, updates or deletes in one statement. It suits loading a staging table into a main table. OUTPUT shows what happened to each row.

SQL
MERGE dbo.Products WITH (HOLDLOCK) AS t
USING staging.Products AS s
    ON t.Sku = s.Sku
WHEN MATCHED AND (t.Name <> s.Name OR t.Price <> s.Price) THEN
    UPDATE SET Name = s.Name, Price = s.Price
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Sku, Name, Price) VALUES (s.Sku, s.Name, s.Price)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT $action, inserted.Sku, deleted.Sku;   -- MERGE must end with ;

MERGE pitfalls

  • MERGE is not safe from races on its own; add WITH (HOLDLOCK) on the target.
  • If two source rows match the same target row, MERGE fails. Remove duplicates from the source first.
  • Put filters for the source in a CTE, not in the ON clause, or rows can be wrongly deleted.
  • For a single row, the UPDATE-then-INSERT pattern is simpler and easier to read.
  • In EF Core 7 and later, ExecuteUpdate covers bulk updates; libraries add bulk upsert on top.

Interview question

Why can "IF NOT EXISTS ... INSERT" still create duplicates?

The check and the insert are separate steps. Two sessions can run the check at the same time, both see no row, and both insert. Fix it by holding a key-range lock for the whole operation (UPDLOCK and HOLDLOCK inside a transaction), and back it up with a unique constraint so a race can never store a duplicate.

Your progress is saved only in this browser.

Want the full details? Read the official Microsoft documentation for this topic.