MERGE geisterte unter dem Begriff „Upsert“, eine Wortschöpfung aus Update und Insert, durch die SQL Server Community. MERGE ist aber mehr als eine Kombination aus INSERT und UPDATE. MERGE ist quasi eine Super DML Klausel, die INSERT, UPDATE und DELETE mit bedingter Ausführung in sich vereint.

MERGE ermittelt anhand von WHEN Bedingungen, ob Datensätze eingefügt, geändert oder gelöscht werden sollen, wobei ein und derselbe Datensatz nicht mehrerer Änderungen unterliegen kann.

Dies bedeutet nicht, dass der MERGE Befehl drei WHEN Klauseln benötigt. MERGE ist um einiges flexibler, dazu später mehr.

Zum Erklären des syntaktischen Aufbaus werden Testdaten erzeugt. (Beispiel lauffähig)

use tempdb

create table dbo.Source_table
(
     ID             int                                     not NULL
    ,Val            varchar(50)
    ,LastChange     datetime   default(current_timestamp)   not NULL
    ,ChangeAction   varchar(1) default('I')                 not NULL
    ,constraint PK_Source_table primary key clustered(ID)
    ,constraint chkAction_Source_table check(ChangeAction in ('U', 'I', 'D'))
)

insert into dbo.Source_table values
(1, 'UPDATED in SOURCE', '2008-08-31', 'U'),
(2, 'UPDATED in SOURCE', '2008-01-01', 'U'),
(3, 'UPDATED in SOURCE', '2008-09-01', 'U'),
(4, 'UPDATED in SOURCE', '2008-05-01', 'U'),
(5, 'UPDATED in SOURCE', '2008-09-12', 'U'),
(6, 'DELETED in SOURCE', '2008-08-31', 'D'),
(7, 'DELETED in SOURCE', '2008-09-15', 'D'),
(8, 'INSERTED in SOURCE', '2008-09-15', 'I'),
(10, 'DATATRASH', '1900-01-01', 'U')

create table dbo.Target_table
(
     ID             int                                     not NULL
    ,Val            varchar(50)
    ,LastChange     datetime   default(current_timestamp)   not NULL
    ,ChangeAction   varchar(1) default('I')                 not NULL
    ,Archive        bit        default(0)                   not NULL
    ,constraint PK_Target_table primary key clustered(ID)
    ,constraint chkAction_Target_table check(ChangeAction in ('U', 'I', 'D'))
)

insert into dbo.Target_table values
(1, 'INSERTED in TARGET', '2008-07-31', 'I', 0),
(2, 'UPDATED in TARGET', '2008-09-01', 'U', 0),
(3, 'UPDATED in TARGET', '2008-06-17', 'U', 0),
(4, 'DELETED in TARGET', '2008-06-17', 'D', 0),
(5, 'DELETED in TARGET', '2008-07-06', 'D', 0),
(6, 'UPDATED in TARGET', '2008-09-01', 'U', 0),
(7, 'UPDATED in TARGET', '2008-06-17', 'U', 0),
(9, 'MISSING IN SOURCE', '2008-06-17', 'I', 0),
(11, 'MISSING IN SOURCE', '2007-03-14', 'I', 0)

Mittels MERGE soll nun die Target_table mit den Daten aus Source_table nach bestimmten Anforderungen aktualisiert werden.
Grundlege Anweisung ist die Angabe der Zieltabelle (merge into), der Quelltabelle (using) und der/die Schlüsselfelder, über die verknüpft wird (on).

merge into dbo.Target_table TRG
using dbo.Source_table SRC
on
    TRG.ID = SRC.ID

Mittels WHEN werden die Bedingungen angegeben, welcher DML Befehl ausgeführt werden soll.

Hier kann es zwei Hauptunterscheidungen geben: WHEN MATCHED und WHEN NOT MATCHED.
WHEN NOT MATCHED kann selbst auch zwei Unterscheidungen haben: WHEN NOT MATCHED BY SOURCE und WHEN NOT MATCHED BY TARGET. Zusätzlich können weitere Bedingungen angegeben werden.
Also, folgende grundsätzliche Konstellationen:

• der Satz ist sowohl im Ziel, als auch in der Quelle,
• der Satz ist im Ziel, aber nicht in der Quelle oder
• der Satz ist in der Quelle aber nicht im Ziel.

Nach folgenden Anforderungen soll das MERGE Beispiel implementiert werden:
1. Wenn der Datensatz in Quelle und Ziel vorhanden ist,
1.1. lösche den Satz wenn ChangeAction in der Quelle D ist,
1.2. aktualisere den Satz wenn das Änderungsdatum jünger als das im Ziel ist.

2. Wenn der Datensatz nur in der Quelle vorhanden ist und ChangeAction I ist, füge den Satz im Ziel ein.

3. Wenn der Datensatz im Ziel, aber nicht in der Quelle vorhanden ist,
3.1. aktualisiere das Archivkennzeichen des Satzes auf 1, wenn die letzte Änderung nach dem 31.12.2007 war,
3.2. lösche den Satz, wenn die letzte Änderung vor dem 01.01.2008 war.

merge into dbo.Target_table TRG
using dbo.Source_table SRC
on
    TRG.ID = SRC.ID

when matched and SRC.ChangeAction = 'D' then
    delete

when matched and SRC.LastChange > TRG.LastChange then
    update set
         TRG.Val            = SRC.VAL
        ,TRG.LastChange     = SRC.LastChange
        ,TRG.ChangeAction   = SRC.ChangeAction

when not matched by target and SRC.ChangeAction = 'I' then
    insert
    ( 
         ID
        ,Val
    )
    values
    (
         SRC.ID
        ,SRC.Val
    )

when not matched by source and TRG.LastChange > '2007-12-31' then
    update set
        TRG.Archive = 1

when not matched by source and TRG.LastChange < '2008-01-01' then
    delete

output
     $action                 action
    ,SRC.ChangeAction
    ,inserted.ID             inserted_ID          
    ,deleted.ID              deleted_ID          
    ,inserted.Val            inserted_Val            
    ,deleted.Val             deleted_Val            
    ,inserted.LastChange     inserted_LastChange     
    ,deleted.LastChange      deleted_LastChange     
    ,inserted.ChangeAction   inserted_ChangeAction   
    ,deleted.ChangeAction    deleted_ChangeAction   
    ,inserted.Archive        inserted_Archive        
    ,deleted.Archive         deleted_Archive;

Wie hier zu erkennen ist werden 5 bedingte DML Kommandos abgesetzt. Die ist die maximale Anzahl. Es ist lediglich eine WHEN Klausel notwendig und damit auch nur ein DML Kommando. Man kann MERGE auf also diverse Anwendungsfälle anpassen.

Folgendes ist zu beachten:

• Die zweite WHEN Klausel für “when matched” und “when not matched by source” wird nur dann ausgeführt, wenn die Bedingung der ersten WHEN Klausel nicht zutraf (genauso wie bei CASE).
• Wenn eine zweite WHEN Klausel für “when matched” und “when not matched by source” angegeben wird, dann muss die erste WHEN Klausel zwingend mit einer AND Bedingung erweitert werden (sonst würde die zweite ja auch nie ausgeführt werden).
• TOP kann auch bei MERGE verwendet werden. Das Ergebnis ist aber nicht vorhersehbar, da keine Sortierung vorgegeben werden kann.
• @@rowcount enthält die Anzahl aller verarbeitenden Datensätze
• IGNORE_DUP_KEY Einstellung wird ignoriert
• Constraints* werden beachtet und Trigger ausgeführt
• Ohne Semikolon am Ende des MERGE Statements läuft nichts…
  (Weiterführend siehe BOL)

Wie man im obigen Beispiel sieht, kann bei der OUTPUT Klausel mittels $ACTION die von MERGE durchgeführte Aktion ausgeben werden. Der Datentyp ist NVARCHAR(10) und enthält UPDATE, INSERT oder DELETE als Text. Zusätzlich zu den systemeigenen Tabellen INSERTED und DELETED hat man Zugriff auf die Spalten der Datenquelle. Dies ist nützlich um Steuerungsfunktionen (Beispiel Löschung, Priorität) der Quelle zu protokollieren.

Da MERGE ein DML Befehl ist, kann natürlich INSERT over DML für MERGE sinnvoll eingesetzt werden.

Performance von MERGE.
In der ON Klausel sollten nur Spalten angeben werden, die zur Verknüpfung erforderlich sind. Optimierungsversuche an dieser Stelle, beispielsweise durch Filter können zu unvorhersehbaren Ergebnissen führen. Idealerweise sind auf den Verknüpfungsspalten identisch definierte clustered Indizes. Im Idealfall wird MERGE mittels eines FULL OUTER MERGE JOIN vom Optimizer geplant (was der Fall mit den clustered Indizes sein wird).

Im letzten Artikel wird ein Anwendungsbeispiel gezeigt, welches mittels Table Valued Parameters und MERGE ein als Tabelle gespeichertes Ergebnis von GROUPING SETS erweitert und aktuell hält. Selbstverständlich wird mit INSERT over DML die Aktion protokolliert.

*MERGE hat in der SQL Server 2008 Version bis einschließlich RTM 10.0.1600 noch einen Fehler: Es wird keine Referentielle Integrität beachtet. Dieser Fehler steht aber seit 21.08.2008 auf gelöst.

Zum Testen, ob dieser Fehler in der benutzten Version vorliegt, kann nachfolgendes Skript benutzt werden:

/**********************************************************************************
Dies ist leicht veränderter Code von
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=357419
**********************************************************************************/
use tempdb;
set nocount on;
go
if object_id('t2', 'U') is not NULL drop table dbo.t2;
if object_id('t1', 'U') is not NULL drop table dbo.t1;
go
create table dbo.t1(c1 int not null primary key, c2 int not null unique);
create table dbo.t2(c1 int not null, c2 int not null references t1(c2), primary key(c1, c2));
go
insert into dbo.t1(c1, c2) values(1, 1);
insert into dbo.t2(c1, c2) values(1, 1);
go

begin try

    update dbo.t1
    set c2 = 2
    where c1 = 1;
    
    select 'If this message occure, the check for foreign key violation fails with update'

end try

begin catch

    select 'update fails with FK violation as it should';

end catch

go

begin try

    merge into dbo.t1
    using (select 1 as c1) as d
    on t1.c1 = d.c1
    when matched then
    update set t1.c2 = 2;

    select 'If this message occure, the check for foreign key violation fails with merge'
    union all
    select 'http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=357419';
    
end try

begin catch

    select 'merge fails with FK violation as it should';

end catch