Stop hand-writing MERGE. Map the columns, tick the keys, copy the statement.

A correct MERGE is forty lines of things to get wrong.

The column list appears in four clauses that must stay in sync. S.Name <> T.Name silently misses changes to and from NULL. Without HOLDLOCK two parallel loads insert the same key twice. A duplicate key in the source either fails with error 8672 or, in a type 2 dimension, quietly writes wrong history. sqmMergeGenerator reads the metadata of both tables and writes all of it.

What it needs.

Three columns side by side, the statement right below.

Show-sqmMergeGeneratorGui connects with Windows or SQL authentication, then offers database and table per combo box for both sides. Source and target may sit in different databases on the same instance; every name is generated three-part.

1. Source columnsEvery column with its data type and a [PK] marker. Tick what to use. Views are offered as a source.
2. Mapping source → targetTarget column per source column, auto-mapped by name (case-insensitive), both data types, and the change type per column. Computed, rowversion and GENERATED ALWAYS columns are never offered as a target.
3. Key columns (join)Preselected from the target primary key. If that is a surrogate key that isn't mapped, the source primary key is used instead, the typical type 2 dimension case.
4. Options and statementEvery change regenerates the statement. Copy it, save it as .sql, check its syntax (compiled with SET NOEXEC ON, names resolved, nothing executed) or execute it after a confirmation.

The target becomes a copy of the source, and a re-run changes nothing.

MERGE INTO [DWH].[dbo].[Customer] WITH (HOLDLOCK) AS T
USING (
    SELECT [CustomerNo], [Name], [City]
    FROM [Staging].[dbo].[Customer]
) AS S
    ON T.[CustomerNo] = S.[CustomerNo]
WHEN MATCHED AND EXISTS (SELECT S.[Name], S.[City]
        EXCEPT
        SELECT T.[Name], T.[City]) THEN          -- NULL-safe: only real changes
    UPDATE SET T.[Name] = S.[Name], T.[City] = S.[City]
WHEN NOT MATCHED BY TARGET THEN
    INSERT ([CustomerNo], [Name], [City])
    VALUES (S.[CustomerNo], S.[Name], S.[City])
WHEN NOT MATCHED BY SOURCE THEN                         -- optional
    DELETE
OUTPUT $action AS MergeAction, COALESCE(inserted.[CustomerNo], deleted.[CustomerNo]) AS [CustomerNo];

Unchanged rows are not touched at all: no log volume, no trigger work, no new rowversion. Columns marked Fixed are only written on insert.

History where it matters, overwrite where it doesn't, per column.

A target with a current flag and/or a valid-to column (several German and English naming patterns are recognised) switches the GUI into type 2 mode. The key is the business key, the surrogate key is assigned by the table.

What the generated statement does, in one transaction

1. Duplicate key checkTHROW 50001 if the source delivers a business key twice, before anything changes.
2. Type 1 columnsOne UPDATE across every version of the key, only where the value really differs.
3. MERGE against the current versionNew keys are inserted, a type 2 change closes the current version, optionally keys missing from the source are closed too. The affected rows go to #smgMergeOutput via OUTPUT INTO.
4. New versionsA plain INSERT from the temp table for every closed key that is still in the source.
5. SummaryOne row: new keys, new versions, closed keys, type 1 updates.

Why not the textbook pattern?

The well-known INSERT INTO dim SELECT ... FROM (MERGE ... OUTPUT ...) is composable DML, and SQL Server rejects it as soon as the dimension has a trigger or is referenced by a foreign key from a fact table. That is most real dimensions. sqmMergeGenerator uses OUTPUT INTO a temp table followed by an ordinary INSERT, so both cases work. Both are part of the live test.

Every switch that matters, with sane defaults.

xml, text, ntext, image and CLR types (geography, geometry, hierarchyid) can't be compared by EXCEPT directly; they are cast for the comparison and assigned unchanged.

A pure text generator for pipelines and deployment scripts.

New-sqmMergeStatement needs no database connection. Feed it the mapping and keys, get the statement back as a string.

Install, then the GUI, or one call.

# Installation - AllUsers when elevated, otherwise CurrentUser
Install.cmd

# GUI (or double-click Start-sqmMergeGenerator.cmd without installing)
Show-sqmMergeGeneratorGui -SqlInstance SQL01

# Upsert from a hashtable mapping source = target
New-sqmMergeStatement -SourceDatabase Staging -SourceTable Customer -TargetDatabase DWH -TargetTable Customer `
    -ColumnMapping @{ CustomerNo = 'CustomerNo'; Name = 'Name'; City = 'City' } -KeyColumns CustomerNo -HoldLock

# SCD type 2 with a change type per column
$map = @(
    @{ SourceColumn = 'CustomerNo'; TargetColumn = 'CustomerNo' }
    @{ SourceColumn = 'Name';       TargetColumn = 'Name';    ChangeType = 'Type1' }
    @{ SourceColumn = 'City';       TargetColumn = 'City';    ChangeType = 'Type2' }
    @{ SourceColumn = 'Segment';    TargetColumn = 'Segment'; ChangeType = 'Fixed' }
)
New-sqmMergeStatement -SourceDatabase Staging -SourceTable Customer -TargetDatabase DWH -TargetSchema dim -TargetTable Customer `
    -ColumnMapping $map -KeyColumns CustomerNo -Mode Scd2 -DuplicateKeyCheck -HoldLock `
    -ValidFromColumn ValidFrom -ValidToColumn ValidTo -CurrentFlagColumn IsCurrent

The last servers and the login name are remembered in %APPDATA%\sqmMergeGenerator\config.json; the password never is. The included live test (Tests\Test-sqmMergeGenerator.ps1, 36 checks) creates two throwaway databases, runs the generated statements against them and drops them again.

Generate your first MERGE in under a minute.

No dependencies to install: unzip, double-click Start-sqmMergeGenerator.cmd, connect.