How to Use the MERGE Statement for Slowly Changing Dimensions

Why MERGE Is Tempting Here

A dimension load is the textbook MERGE workload. You have a staging table with the current state from the source, a dimension table with what you loaded yesterday, and three things to do: insert new business keys, update changed ones, and leave the rest alone. That is precisely what MERGE expresses in a single readable statement.

For a Type 1 dimension, where a change simply overwrites the old value, it really is that easy and MERGE is a perfectly good choice. For Type 2 it takes one more trick, and a few decisions you should make deliberately rather than discover in production.

The Setup

-- Current state from the source system, no history
CREATE TABLE stg.Customer (
  CustomerID   INT           NOT NULL PRIMARY KEY,
  CustomerName NVARCHAR(100) NOT NULL,
  City         NVARCHAR(60)  NULL,
  Segment      NVARCHAR(20)  NOT NULL
);

-- Type 2 dimension
CREATE TABLE dim.Customer (
  CustomerKey  INT IDENTITY(1,1) NOT NULL PRIMARY KEY,  -- surrogate key, per version
  CustomerID   INT           NOT NULL,                  -- business key, repeats
  CustomerName NVARCHAR(100) NOT NULL,
  City         NVARCHAR(60)  NULL,
  Segment      NVARCHAR(20)  NOT NULL,
  ValidFrom    DATE          NOT NULL,
  ValidTo      DATE          NOT NULL,   -- '9999-12-31' while current
  IsCurrent    BIT           NOT NULL,
  IsDeleted    BIT           NOT NULL CONSTRAINT DF_dimCustomer_IsDeleted DEFAULT (0),
  RowHash      BINARY(32)    NOT NULL
);

-- One current version per business key, enforced by the engine
CREATE UNIQUE INDEX UX_dimCustomer_Current
  ON dim.Customer (CustomerID) WHERE IsCurrent = 1;

Type 1 with MERGE: The Easy Case

Overwrite in place, insert what is new, soft-delete what vanished from the source:

MERGE dim.Product WITH (HOLDLOCK) AS tgt
USING stg.Product AS src
   ON tgt.ProductID = src.ProductID

WHEN MATCHED AND EXISTS (
       SELECT src.ProductName, src.Category, src.ListPrice
       EXCEPT
       SELECT tgt.ProductName, tgt.Category, tgt.ListPrice )
  THEN UPDATE SET tgt.ProductName = src.ProductName,
                  tgt.Category    = src.Category,
                  tgt.ListPrice   = src.ListPrice

WHEN NOT MATCHED BY TARGET
  THEN INSERT (ProductID, ProductName, Category, ListPrice, IsDeleted)
       VALUES (src.ProductID, src.ProductName, src.Category, src.ListPrice, 0)

WHEN NOT MATCHED BY SOURCE AND tgt.ProductKey <> -1 AND tgt.IsDeleted = 0
  THEN UPDATE SET tgt.IsDeleted = 1;

Two details in there are worth stealing regardless of which SCD type you use.

The first is the EXCEPT trick for change detection. Writing tgt.City <> src.City is wrong the moment a column is nullable: NULL <> 'Berlin' is UNKNOWN, not true, so the row is not treated as changed and the update is silently skipped. EXISTS (SELECT ... EXCEPT SELECT ...) compares the whole tuple with NULL-safe semantics and needs no ISNULL wrapping.

The second is tgt.ProductKey <> -1 in the NOT MATCHED BY SOURCE branch. Key -1 is the Unknown member that every dimension should have; it never appears in the source, so without that predicate the very first load flags it as deleted.

The Type 2 Problem

Type 2 needs two row operations for one source row: close the existing version (UPDATE: set ValidTo and IsCurrent = 0) and open a new one (INSERT). MERGE cannot do that. Each source row triggers exactly one WHEN clause, and the standard explicitly forbids the same target row being acted on twice. Writing both a WHEN MATCHED THEN UPDATE and hoping for an insert does not work.

The way out is the OUTPUT clause. MERGE does the expiring, reports which rows it just expired, and an outer INSERT consumes that report and writes the new versions.

The Pattern: MERGE with OUTPUT, Wrapped in an INSERT

DECLARE @Effective DATE = CAST(SYSUTCDATETIME() AS DATE);

-- Hash only the attributes tracked as Type 2, with NULL sentinels
SELECT CustomerID, CustomerName, City, Segment,
       HASHBYTES('SHA2_256',
         CONCAT(ISNULL(CustomerName, N'~NULL~'), N'|',
                ISNULL(City,         N'~NULL~'), N'|',
                ISNULL(Segment,      N'~NULL~'))) AS RowHash
INTO #src
FROM stg.Customer;

INSERT INTO dim.Customer
      (CustomerID, CustomerName, City, Segment,
       ValidFrom, ValidTo, IsCurrent, IsDeleted, RowHash)
SELECT chg.CustomerID, chg.CustomerName, chg.City, chg.Segment,
       @Effective, '9999-12-31', 1, 0, chg.RowHash
FROM (
    MERGE dim.Customer WITH (HOLDLOCK) AS tgt
    USING #src AS src
       ON tgt.CustomerID = src.CustomerID
      AND tgt.IsCurrent  = 1

    -- Brand new business key: insert the first version directly
    WHEN NOT MATCHED BY TARGET
      THEN INSERT (CustomerID, CustomerName, City, Segment,
                   ValidFrom, ValidTo, IsCurrent, IsDeleted, RowHash)
           VALUES (src.CustomerID, src.CustomerName, src.City, src.Segment,
                   @Effective, '9999-12-31', 1, 0, src.RowHash)

    -- Changed: expire the current version. The new one is inserted by the outer INSERT.
    WHEN MATCHED AND tgt.RowHash <> src.RowHash
      THEN UPDATE SET tgt.ValidTo   = DATEADD(DAY, -1, @Effective),
                      tgt.IsCurrent = 0

    OUTPUT $action AS MergeAction,
           src.CustomerID, src.CustomerName, src.City, src.Segment, src.RowHash
) AS chg (MergeAction, CustomerID, CustomerName, City, Segment, RowHash)
WHERE chg.MergeAction = 'UPDATE';   -- only the rows that were just expired

Read it from the inside out. The MERGE matches on the business key and IsCurrent = 1, so only the live version is a candidate. New customers fall into NOT MATCHED BY TARGET and are inserted with their first version immediately. Changed customers hit WHEN MATCHED, get expired, and are reported by OUTPUT with $action = 'UPDATE', carrying the source values with them. The outer INSERT filters on that action and writes exactly one new current version per expired row. Unchanged customers match nothing and cost nothing.

One statement, one pass over the dimension, correct Type 2 semantics. That is the appeal, and it is real.

The Restrictions Nobody Mentions Until It Fails

Feeding a DML statement's OUTPUT straight into an INSERT is called nested DML, and SQL Server puts hard limits on it. Two of them hit dimension tables directly:

• The outer INSERT target cannot be on either side of a foreign key relationship. If your fact tables have real FKs pointing at dim.Customer, or the dimension itself references another table, the statement fails to compile with "the target table of the INSERT statement cannot be on either side of a (primary key, foreign key) relationship". Many warehouses have no FKs on facts, which is why the pattern is so widely published; if yours does, it is a non-starter.
• The outer INSERT target cannot have enabled triggers, and it cannot have CHECK constraints or enabled rules.
• The outer SELECT must be a simple projection and filter. No JOIN, no GROUP BY, HAVING, DISTINCT, TOP, ORDER BY, UNION, aggregates or subqueries. You get column references and a WHERE, nothing more. If a value needs a lookup, it has to already be in the OUTPUT.

The Workaround: OUTPUT INTO a Table Variable

When one of those restrictions applies, split the statement. The MERGE writes its report into a table variable or temp table, and a separate INSERT reads it. None of the nested-DML limits apply, and you can join, filter and transform freely:

DECLARE @Changes TABLE (
  MergeAction  NVARCHAR(10)  NOT NULL,
  CustomerID   INT           NOT NULL,
  CustomerName NVARCHAR(100) NOT NULL,
  City         NVARCHAR(60)  NULL,
  Segment      NVARCHAR(20)  NOT NULL,
  RowHash      BINARY(32)    NOT NULL
);

BEGIN TRANSACTION;

  MERGE dim.Customer WITH (HOLDLOCK) AS tgt
  USING #src AS src
     ON tgt.CustomerID = src.CustomerID
    AND tgt.IsCurrent  = 1
  WHEN NOT MATCHED BY TARGET
    THEN INSERT (CustomerID, CustomerName, City, Segment,
                 ValidFrom, ValidTo, IsCurrent, IsDeleted, RowHash)
         VALUES (src.CustomerID, src.CustomerName, src.City, src.Segment,
                 @Effective, '9999-12-31', 1, 0, src.RowHash)
  WHEN MATCHED AND tgt.RowHash <> src.RowHash
    THEN UPDATE SET tgt.ValidTo   = DATEADD(DAY, -1, @Effective),
                    tgt.IsCurrent = 0
  OUTPUT $action, src.CustomerID, src.CustomerName, src.City, src.Segment, src.RowHash
    INTO @Changes (MergeAction, CustomerID, CustomerName, City, Segment, RowHash);

  INSERT INTO dim.Customer
        (CustomerID, CustomerName, City, Segment,
         ValidFrom, ValidTo, IsCurrent, IsDeleted, RowHash)
  SELECT CustomerID, CustomerName, City, Segment,
         @Effective, '9999-12-31', 1, 0, RowHash
  FROM @Changes
  WHERE MergeAction = 'UPDATE';

COMMIT TRANSACTION;

This version is longer, and it is the one to reach for in a real warehouse. It survives foreign keys, it survives triggers, and the intermediate result is inspectable when the load misbehaves at three in the morning.

Concurrency: MERGE Is Not Atomic by Default

The single most common production surprise: MERGE does not take a range lock on its own. Under concurrency, two sessions can both evaluate NOT MATCHED for the same key and both insert, producing a primary key violation or a duplicate. It is the same race condition as the naive IF EXISTS ... ELSE INSERT upsert, just harder to spot because the statement looks atomic.

-- Not optional under concurrency
MERGE dim.Customer WITH (HOLDLOCK) AS tgt
USING ...

HOLDLOCK (equivalent to SERIALIZABLE on the target) makes the match-and-act sequence safe. Add it to every MERGE you write, including the ones in this article. It is cheap insurance in a nightly load that has the table to itself, and mandatory in anything that runs alongside other writers.

Two more operational notes for the ETL window:

• Filter the source, not the target. Predicates in the ON clause change matching semantics; predicates in WHEN MATCHED AND ... only filter the action. The IsCurrent = 1 in the examples above sits in ON deliberately, because a business key with no current row must be treated as not matched. Anything that is merely a filter belongs in the WHEN clause or in the source query.
• Watch the plan on large dimensions. MERGE frequently produces a full scan of the target plus a sort, where two separate statements would seek. On a dimension of a few hundred thousand rows this is irrelevant; at tens of millions it is worth comparing the two approaches with real numbers before committing.

MERGE for Type 6

A Type 6 dimension carries versioned columns plus "current" columns that are Type 1 overwritten on every version of a business key. MERGE cannot express that in one statement either, because the Type 1 update has to touch all versions, not just the matched one. Run it as a third step after the pattern above:

-- Step 3: push today's value across every version of the business key
UPDATE d
   SET d.CurrentSegment = s.Segment
FROM dim.Customer AS d
JOIN #src AS s ON s.CustomerID = d.CustomerID
WHERE EXISTS (SELECT s.Segment EXCEPT SELECT d.CurrentSegment);

So Should You Use MERGE at All?

An honest answer, after the pattern is on the table:

MERGE has accumulated a genuinely long trail of fixed and unfixed bugs since 2008, mostly around unique index maintenance, filtered indexes, foreign key handling and incorrect results under specific plan shapes. None of that makes it unusable, and Microsoft has fixed a lot of it, but it does mean the statement deserves more testing than its readability suggests it needs.

A reasonable default: use MERGE for Type 1 dimensions and for small to mid-size Type 2 dimensions where the elegance genuinely helps, always with HOLDLOCK, and prefer the OUTPUT INTO variant over nested DML. For the largest dimensions, and anywhere the load must be restartable and auditable step by step, the plain expire-then-insert pair is easier to reason about and easier to fix. What matters far more than this choice is that the load is set-based at all, which is the actual reason to keep the SSIS SCD wizard out of it.

Validation After Every Load

-- More than one current version per business key: should return zero rows
SELECT CustomerID, COUNT(*) AS CurrentRows
FROM dim.Customer
WHERE IsCurrent = 1
GROUP BY CustomerID
HAVING COUNT(*) > 1;

-- Gaps or overlaps in the timeline: should return zero rows
SELECT CustomerID, ValidFrom, ValidTo, PrevValidTo
FROM (
  SELECT CustomerID, ValidFrom, ValidTo,
         LAG(ValidTo) OVER (PARTITION BY CustomerID ORDER BY ValidFrom) AS PrevValidTo
  FROM dim.Customer
) AS x
WHERE PrevValidTo IS NOT NULL
  AND ValidFrom <> DATEADD(DAY, 1, PrevValidTo);

Checklist

• ☐ Every MERGE carries WITH (HOLDLOCK) on the target
• ☐ Change detection is NULL-safe: a hash with sentinels, or EXISTS (SELECT ... EXCEPT SELECT ...)
• ☐ The ON clause contains matching logic only; everything else lives in WHEN MATCHED AND ...
• ☐ WHEN NOT MATCHED BY SOURCE is used only against a full snapshot, and never hard-deletes
• ☐ The Unknown member (-1) is excluded from every delete or expire branch
• ☐ A filtered unique index on IsCurrent = 1 backs up the logic at the engine level
• ☐ If the dimension has foreign keys or triggers, use OUTPUT INTO instead of nested DML
• ☐ The whole load runs in one transaction and is safe to re-run after a failure
• ☐ The duplicate-current and gap/overlap checks run afterwards and block publication on failure
• ☐ The execution plan was compared against separate statements on production-size data before it shipped

The Bottom Line

MERGE can absolutely load a Type 2 dimension, and the OUTPUT-plus-outer-INSERT pattern is the way to do it in a single pass. What it cannot do is make the decision simple: nested DML falls over on foreign keys, missing HOLDLOCK turns the statement into a race condition, and the elegance disappears the moment you need to debug why 400 customers got a new version last night. Write it with OUTPUT INTO, wrap it in a transaction, validate afterwards, and treat the one-statement version as a nice-to-have rather than the goal.