sqmMergeGenerator v0.1.0.0

T-SQL MERGE
without writing it by hand

Pick source and target, map the columns SSIS-style, tick the keys. The statement is regenerated on every click.

modes: upsert and SCD type 2

change types per column

module dependencies

live test checks

Source table

→

Column mapping

→

Join keys

→

MERGE statement

The problem

A correct MERGE is forty lines of things to get wrong

By hand

Column list typed in four clauses, kept in sync manually
S.Name <> T.Name misses changes to and from NULL
No HOLDLOCK: parallel loads insert the same key twice
Duplicate source key: error 8672, or wrong history in a dimension
SCD 2 textbook pattern breaks on triggers and foreign keys

With sqmMergeGenerator

Every clause generated from one mapping
NULL-safe EXISTS (... EXCEPT ...), re-run touches 0 rows
WITH (HOLDLOCK) on by default
THROW 50001 before anything changes
OUTPUT INTO #temp + INSERT, one transaction

The GUI

Three columns side by side, the statement below

sqmMergeGenerator GUI with source columns, mapping grid, key columns, options and the generated statement

1Source columns: tick what to use, views allowed as source

2Mapping: target column auto-mapped by name, both data types, change type per column

3Keys: preselected from the target PK, source PK if the surrogate key isn't mapped

Copy, save as .sql, syntax check with SET NOEXEC ON, or execute after confirmation

Mode 1

Upsert / SCD type 1

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 DELETE                    -- optional
OUTPUT $action AS MergeAction, COALESCE(inserted.[CustomerNo], deleted.[CustomerNo]);

✓ Unchanged rows untouched: no log, no triggers, no new rowversion

✓ Identity column mapped: IDENTITY_INSERT wrapped automatically

Mode 2

Slowly changing dimension type 2, per column

Change type	Upsert / SCD 1	SCD type 2
Type 2: history	overwrite	Change closes the current version and inserts a new one
Type 1: overwrite	overwrite	Overwritten in all versions of the key, no new version
Fixed	insert only	Never updated, only set on the first insert

Current flag

IsCurrent, IstAktuell: 1 for the current version

Valid to

ValidTo, GueltigBis, EndDate: open end NULL or '9999-12-31'

Valid from

ValidFrom, GueltigAb, StartDate: optional

Target has these columns? The GUI detects them by name and switches to SCD type 2 on its own.

Mode 2: how it runs

One transaction, five steps

Duplicate key check

→

Type 1 UPDATE

→

MERGE vs. current version

→

INSERT new versions

→

Summary row

Why not composable DML?

INSERT INTO dim SELECT ... FROM (MERGE ... OUTPUT ...) is rejected by SQL Server as soon as the dimension has a trigger or is referenced by a foreign key from a fact table. That is most real dimensions.

What it does instead

MERGE ... OUTPUT INTO #smgMergeOutput, then a plain INSERT for every closed key still in the source. XACT_ABORT ON, one transaction, triggers and FKs both covered by the live test.

Summary row (column names as generated)

NeueSchluessel · NeueVersionen · GeschlosseneSchluessel · Typ1Aktualisiert

Options

Every switch that matters, sane defaults

Option	Effect	Default
Update only on real change	`WHEN MATCHED AND EXISTS (... EXCEPT ...)`	on
Not in source	Upsert: DELETE · SCD 2: close the current version	off
WITH (HOLDLOCK)	No race between parallel loads	on
OUTPUT $action	Action and key per row (SCD 2: summary row)	off
Duplicate key check	`THROW 50001` before anything changes	on
Source filter	`WHERE` inside the USING subquery	empty

xml, text, ntext, image and CLR types are cast for the comparison, assigned unchanged.

Scripting

Same generator, no GUI, no connection

$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

Function	Purpose
Show-sqmMergeGeneratorGui	The GUI, optionally connected right away
New-sqmMergeStatement	Pure text generator for pipelines and deployment scripts
Get-sqmMergeDatabase / -Table / -Column	Metadata via System.Data.SqlClient

Requirements and safety

Runs where dbatools isn't allowed

Requirements

Windows PowerShell 5.1 or PowerShell 7 (Windows)
SQL Server 2012 or later, tested on 2022
No dbatools, no SqlServer module, no PSGallery
Read access to the catalog views to generate

Safety

Generates text, runs nothing on its own
Execute only after an explicit confirmation
Password never written to the config file
All names bracket-quoted, ] escaped

Live test: two throwaway databases, 36 checks incl. idempotent re-run, xml change detection, identity insert, triggers and FKs on the dimension, duplicate key check.

Summary

Map the columns, tick the keys, copy the statement

modes

change types

dependencies

live checks

✓ NULL-safe upsert, idempotent re-runs
✓ SCD type 2 with history, overwrite and fixed columns
✓ Works with triggers and foreign keys

✓ Duplicate key check, HOLDLOCK, IDENTITY_INSERT
✓ Syntax check without executing anything
✓ GUI or script, PowerShell 5.1 and 7

GitHub: JankeUwe/sqmMergeGenerator powershelldba.de/sqmmergegenerator

T-SQL MERGEwithout writing it by hand