Why I Swapped Separate UPDATE/INSERT for MERGE

I used to handle "upsert" logic with a two‑step pattern: first try to UPDATE a row, then fall back to INSERT if the UPDATE affected zero rows. In a high‑traffic inventory system that pattern turned into a race condition nightmare, and the audit log quickly became a mess of duplicated entries. One day I switched to the MERGE statement and never looked back. Not only did the logic become atomic, but adding an audit trail became a one‑liner.

The Real‑World Pain

Our product catalog receives price updates from multiple suppliers every few minutes. The database table Products stores the latest known price, last updated timestamp, and a version number. When a new feed arrives, we must:

  • Update the price and timestamp if a product already exists.
  • Insert a new product row if we haven’t seen the SKU before.
  • Record every change (old values, new values, user, timestamp) into an AuditProducts table.

Writing this as separate statements meant we could not guarantee that the audit entry matched the data change, especially under concurrent loads. The version column often ended up out of sync, and debugging became a nightmare.

The MERGE Solution

The MERGE statement lets you treat a source and a target as a single logical operation. It guarantees that either all modifications happen together or none at all, and we can embed an OUTPUT clause to capture changed rows directly into our audit table. This eliminates the need for triggers and gives us full control over the audit payload.

Use MERGE when you need an atomic "update or insert" with side‑effects like logging, and you want to avoid race conditions and duplicate audit entries.

Production‑Ready Example

Below is a complete, commented script that demonstrates the pattern. I keep the comments explicit because the syntax is dense and we want future maintainers to see the intent at a glance.


/*
   Upsert product data and audit the change in a single atomic operation.
   Input parameters:
     @Sku          – the product SKU (unique key)
     @NewPrice     – incoming price from the supplier
     @UpdatedBy    – user or feed identifier performing the update
*/
CREATE PROCEDURE dbo.UpsertProductWithAudit
    @Sku        NVARCHAR(50),
    @NewPrice   DECIMAL(18,6),
    @UpdatedBy  NVARCHAR(100)
AS
BEGIN
    /*
       Define the source data that we are trying to merge into the target.
       This is the "new" state of the row.
    */
    DECLARE @Source AS TABLE
    (
        Sku        NVARCHAR(50) PRIMARY KEY,
        NewPrice   DECIMAL(18,6),
        UpdatedBy  NVARCHAR(100),
        UpdatedOn  DATETIME2 = SYSDATETIME()
    );

    INSERT INTO @Source (Sku, NewPrice, UpdatedBy)
    VALUES (@Sku, @NewPrice, @UpdatedBy);

    /*
       The MERGE statement compares the source rows against the target
       (Products) using the business key Sku. Three actions are possible:
         - WHEN MATCHED   : price changed → update
         - WHEN NOT MATCHED: new SKU → insert
       Both branches capture the before/after state for auditing.
    */
    MERGE INTO dbo.Products AS Target
    USING @Source AS Source
    ON Target.Sku = Source.Sku
    WHEN MATCHED AND Target.Price <> Source.NewPrice
    THEN UPDATE SET
            Target.Price = Source.NewPrice,
            Target.LastUpdated = Source.UpdatedOn,
            Target.Version = Target.Version + 1
    WHEN NOT MATCHED
    THEN INSERT (Sku, Price, LastUpdated, Version, CreatedBy, CreatedOn)
         VALUES (Source.Sku,
                 Source.NewPrice,
                 Source.UpdatedOn,
                 1,                     -- start version at 1 for new rows
                 Source.UpdatedBy,
                 Source.UpdatedOn);

    /*
       Capture the change for auditing. The OUTPUT clause runs *during* the
       MERGE, so we can refer to "Inserted" (the new row) and "Deleted"
       (the old row, if any). This guarantees a one‑to‑one mapping between the
       data change and the audit entry.
    */
    OUTPUT
        CASE
            WHEN Inserted.Sku IS NULL THEN 'DELETE'   -- not used in this logic
            WHEN Deleted.Sku IS NULL   THEN 'INSERT'
            ELSE 'UPDATE'
        END                                   AS ChangeType,
        COALESCE(Deleted.Sku, Inserted.Sku) AS Sku,
        COALESCE(Deleted.Price, CAST(0 AS DECIMAL(18,6))) AS OldPrice,
        COALESCE(Inserted.Price, CAST(0 AS DECIMAL(18,6))) AS NewPrice,
        COALESCE(Deleted.LastUpdated, CAST('1900-01-01' AS DATETIME2)) AS OldLastUpdated,
        COALESCE(Inserted.LastUpdated, CAST('1900-01-01' AS DATETIME2)) AS NewLastUpdated,
        COALESCE(Deleted.Version, 0) AS OldVersion,
        COALESCE(Inserted.Version, 0) AS NewVersion,
        Source.UpdatedBy                 AS UpdatedBy,
        SYSDATETIME()                    AS AuditedOn
    INTO dbo.AuditProducts;
END;
GO

After creating the procedure, using it is straightforward:


EXEC dbo.UpsertProductWithAudit @Sku = N'ABC-123', @NewPrice = 19.99, @UpdatedBy = N'SupplierFeed';

Why MERGE Beats the Old Pattern

  • Atomicity – The UPDATE and INSERT are wrapped in a single transaction internally, so you never see a half‑applied state.
  • Clean Auditing – The OUTPUT clause gives us the exact before/after rows without extra queries or triggers.
  • Readability – One statement expresses intent clearly, whereas the old pattern required multiple statements and conditional logic.
  • Performance – The engine can evaluate the join once and apply all changes in a single pass, which is often faster than separate UPDATE/INSERT calls.

I also appreciate that MERGE works across virtually all modern RDBMS that support T‑SQL (SQL Server), PL/pgSQL (PostgreSQL), and Oracle. The exact syntax may differ slightly, but the pattern remains the same.

Tips and Gotchas

  1. Always include a WHERE predicate in the WHEN MATCHED clause if you only want to update on actual changes. This prevents unnecessary writes and version churn.
  2. Use OUTPUT to write directly into the audit table rather than using triggers. Triggers fire after the fact and can be harder to debug when the MERGE itself is complex.
  3. Be mindful of the WITH (TABLOCK) hint if you anticipate heavy concurrency on the target table. MERGE can take an exclusive lock on the target by default, which may affect performance.
  4. When porting to other databases, remember that the OUTPUT clause may be expressed as RETURNING (PostgreSQL) or RETURNING (Oracle). The pattern of using a derived table for source data stays consistent.

When to Stick with Separate Statements

There are rare cases where the old UPDATE/INSERT pattern still makes sense. If you need to apply business logic that differs per row (e.g., a complex calculation that cannot be expressed in a SET operation), or you are using a database that does not support MERGE, then separate statements give you more flexibility. In our inventory scenario, however, the logic is static and the atomic guarantee is critical, so MERGE is the clear winner.

Wrapping Up

The MERGE statement is more than a convenience; it’s a building block for reliable data integration. By pairing it with an OUTPUT clause, we can keep audit trails accurate without the overhead of triggers. I now default to MERGE for any upsert that also requires logging, and the code is cleaner, faster, and easier to reason about. Try it on your next data‑loading task and see the difference for yourself.