MERGE (Transact-SQL)

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics

Die MERGE-Anweisung führt Einfüge-, Aktualisierungs- oder Löschvorgänge in einer Zieltabelle anhand der Ergebnisse eines Joins mit einer Quelltabelle aus. Synchronisieren Sie z.B. zwei Tabellen, indem Sie Zeilen in einer Tabelle anhand von Unterschieden, die in der anderen Tabelle gefunden wurden, einfügen, aktualisieren oder löschen.

INSERT tbl_A (col, col2)
SELECT col, col2
FROM tbl_B
WHERE NOT EXISTS (SELECT col FROM tbl_A A2 WHERE A2.col = tbl_B.col);

Transact-SQL-Syntaxkonventionen

Syntax

[ WITH <common_table_expression> [,...n] ]
MERGE
    [ TOP ( expression ) [ PERCENT ] ]
    [ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]
    USING <table_source> [ [ AS ] table_alias ]
    ON <merge_search_condition>
    [ WHEN MATCHED [ AND <clause_search_condition> ]
        THEN <merge_matched> ] [ ...n ]
    [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]
        THEN <merge_not_matched> ]
    [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]
        THEN <merge_matched> ] [ ...n ]
    [ <output_clause> ]
    [ OPTION ( <query_hint> [ ,...n ] ) ]
;

<target_table> ::=
{
    [ database_name . schema_name . | schema_name . ] [ [ AS ] target_table ]
    | @variable [ [ AS ] target_table ]
    | common_table_expression_name [ [ AS ] target_table ]
}

<merge_hint>::=
{
    { [ <table_hint_limited> [ ,...n ] ]
    [ [ , ] { INDEX ( index_val [ ,...n ] ) | INDEX = index_val }]
    }
}

<merge_search_condition> ::=
    <search_condition>

<merge_matched>::=
    { UPDATE SET <set_clause> | DELETE }

<merge_not_matched>::=
{
    INSERT [ ( column_list ) ]
        { VALUES ( values_list )
        | DEFAULT VALUES }
}

<clause_search_condition> ::=
    <search_condition>

[ WITH <common_table_expression> [,...n] ]
MERGE
    [ INTO ] <target_table> [ [ AS ] table_alias ]
    USING <table_source> [ [ AS ] table_alias ]
    ON <merge_search_condition>
    [ WHEN MATCHED [ AND <clause_search_condition> ]
        THEN <merge_matched> ] [ ...n ]
    [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]
        THEN <merge_not_matched> ]
    [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]
        THEN <merge_matched> ] [ ...n ]
    [ OPTION ( <query_hint> [ ,...n ] ) ]
;  -- The semi-colon is required, or the query will return a syntax error.

<target_table> ::=
{
    [ database_name . schema_name . | schema_name . ]
  target_table
}

<merge_search_condition> ::=
    <search_condition>

<merge_matched>::=
    { UPDATE SET <set_clause> | DELETE }

<merge_not_matched>::=
{
    INSERT [ ( column_list ) ]
        VALUES ( values_list )
}

<clause_search_condition> ::=
    <search_condition>

Argumente

WITH <common_table_expression>

Gibt den temporären Resultset- oder Sichtnamen an, der auch als allgemeiner Tabellenausdruck bezeichnet wird und innerhalb der MERGE-Anweisung definiert ist. Das Resultset wird aus einer einfachen Abfrage abgeleitet. Die MERGE-Anweisung verweist auf dieses Resultset. Weitere Informationen finden Sie unter WITH common_table_expression (Transact-SQL).

database_name

Der Name der Datenbank, in der sich target_table befindet.

schema_name

Der Namen des Schemas, zu dem die Tabelle target_table gehört.

target_table

Hiermit wird die Tabelle oder die Ansicht angegeben, mit der die Datenzeilen aus <table_source> basierend auf <clause_search_condition> abgeglichen werden. target_table ist das Ziel aller Einfüge-, Update- oder Löschvorgänge, die durch die WHEN-Klauseln der MERGE-Anweisung angegeben werden.

Wenn target_table eine Sicht ist, müssen alle Aktionen für die Tabelle die Bedingungen zum Aktualisieren von Sichten erfüllen. Weitere Informationen finden Sie unter Modify Data Through a View (Ändern von Daten über eine Sicht).

target_table darf keine Remotetabelle sein. Für target_table dürfen keine Regeln definiert sein. target_table darf keine speicheroptimierte Tabelle sein.

Hinweise können als <merge_hint> angegeben werden.

[ AS ] table_alias

Dies ist ein alternativer Name zum Verweisen auf eine Tabelle für target_table.

USING <table_source>

Hiermit wird die Datenquelle angegeben, die basierend auf <merge_search_condition> mit den Datenzeilen in target_table abgeglichen wird. Das Ergebnis dieser Zuordnung legt die Aktionen fest, die von den WHEN-Klauseln der MERGE-Anweisung ausgeführt werden. <table_source> kann eine Remotetabelle oder eine abgeleitete Tabelle sein, die auf Remotetabellen zugreift.

[ AS ] table_alias

Dies ist ein alternativer Name zum Verweisen auf eine Tabelle für table_source.

Weitere Informationen zur Syntax und zu den Argumenten dieser Klausel finden Sie unter FROM (Transact-SQL).

ON <merge_search_condition>

Hiermit werden die Bedingungen angegeben, unter denen <table_source> mit target_table verknüpft wird, um Übereinstimmungen zu ermitteln.

WHEN MATCHED THEN <merge_matched>

Hiermit wird angegeben, dass alle Zeilen von *target_table, die mit den von <table_source> ON <merge_search_condition> zurückgegebenen Zeilen übereinstimmen und alle zusätzlichen Suchbedingungen erfüllen, gemäß der <merge_matched>-Klausel aktualisiert oder gelöscht werden.

Die MERGE-Anweisung kann höchstens über zwei WHEN MATCHED-Klauseln verfügen. Wenn zwei Klauseln angegeben werden, muss die erste Klausel von einer AND <search_condition>-Klausel begleitet werden. Für jede gegebene Zeile wird die zweite WHEN MATCHED-Klausel nur angewendet, wenn die erste nicht angewendet wurde. Wenn zwei WHEN MATCHED-Klauseln vorhanden sind, muss die eine eine UPDATE-Aktion und die andere eine DELETE-Aktion angeben. Wenn UPDATE in der <merge_matched>-Klausel angegeben wird und mehr als eine Zeile aus <table_source> basierend auf <merge_search_condition> mit einer Zeile in target_table übereinstimmt, gibt SQL Server einen Fehler zurück. Die MERGE-Anweisung kann dieselbe Zeile nicht mehrmals aktualisieren oder dieselbe Zeile aktualisieren und löschen.

WHEN NOT MATCHED [ BY TARGET ] THEN <merge_not_matched>

Hiermit wird angegeben, dass für jede Zeile, die von <table_source> ON <merge_search_condition> zurückgegeben wird und nicht mit einer Zeile in target_table übereinstimmt, aber eine zusätzliche Suchbedingung erfüllt (falls vorhanden), eine Zeile in target_table eingefügt wird. Die einzufügenden Werte werden durch die <merge_not_matched>-Klausel angegeben. Die MERGE-Anweisung kann nur über eine WHEN NOT MATCHED [ BY TARGET ]-Klausel verfügen.

WHEN NOT MATCHED BY SOURCE THEN <merge_matched>

Hiermit wird angegeben, dass alle Zeilen von *target_table, die nicht mit den von <table_source> ON <merge_search_condition> zurückgegebenen Zeilen übereinstimmen und alle zusätzlichen Suchbedingungen erfüllen, gemäß der <merge_matched>-Klausel aktualisiert oder gelöscht werden.

Die MERGE-Anweisung kann höchstens über zwei WHEN NOT MATCHED BY SOURCE-Klauseln verfügen. Wenn zwei Klauseln angegeben werden, muss die erste Klausel von einer AND <clause_search_condition>-Klausel begleitet werden. Für jede gegebene Zeile wird die zweite WHEN NOT MATCHED BY SOURCE-Klausel nur angewendet, wenn die erste nicht angewendet wurde. Wenn zwei WHEN NOT MATCHED BY SOURCE-Klauseln vorhanden sind, muss die eine eine UPDATE-Aktion und die andere eine DELETE-Aktion angeben. In <clause_search_condition> kann nur auf Spalten aus der Zieltabelle verwiesen werden.

Wenn von <table_source> keine Zeilen zurückgegeben werden, kann auf Spalten in der Quelltabelle nicht zugegriffen werden. Wenn die in der <merge_matched>-Klausel angegebene Update- oder Löschaktion auf Spalten in der Quelltabelle verweist, wird der Fehler 207 (Ungültiger Spaltenname) zurückgegeben. Die Klausel WHEN NOT MATCHED BY SOURCE THEN UPDATE SET TargetTable.Col1 = SourceTable.Col1 kann beispielsweise dazu führen, dass die Anweisung fehlschlägt, da der Zugriff auf Col1 in der Quelltabelle nicht möglich ist.

AND <clause_search_condition>

Gibt jede gültige Suchbedingung an. Weitere Informationen finden Sie unter Suchbedingung (Transact-SQL).

OPTION ( <query_hint> [ ,...n ] )

Gibt an, dass mithilfe von Optimierungshinweisen angepasst wird, wie die Anweisung von Datenbank-Engine verarbeitet wird. Weitere Informationen finden Sie unter Hinweise (Transact-SQL) – Abfrage.

<merge_matched>

Hiermit wird die Update- oder Löschaktion angegeben, die auf alle Zeilen von target_table angewendet wird, die nicht mit den von <table_source> ON <merge_search_condition> zurückgegebenen Zeilen übereinstimmen, und die alle zusätzlichen Suchbedingungen erfüllen.

UPDATE SET <set_clause>

Gibt die Liste der Spalten- oder Variablennamen an, die in der Zieltabelle aktualisiert werden sollen, sowie die Werte, mit denen das Update vorgenommen werden soll.

Weitere Informationen zu den Argumenten dieser Klausel finden Sie unter UPDATE (Transact-SQL). Eine Variable auf denselben Wert festzulegen wie eine Spalte wird nicht unterstützt.

Delete

Gibt an, dass die Zeilen, die mit Zeilen in target_table übereinstimmen, gelöscht werden.

<merge_not_matched>

Gibt die Werte an, die in die Zieltabelle eingefügt werden sollen.

( column_list )

Eine Liste mit einer oder mehreren Spalten der Zieltabelle, in die Daten eingefügt werden sollen. Spalten müssen als einteiliger Name angegeben werden. Andernfalls schlägt die MERGE-Anweisung fehl. column_list muss in Klammern eingeschlossen und durch ein Trennzeichen getrennt werden.

VALUES ( values_list)

Eine durch Trennzeichen getrennte Liste mit Konstanten, Variablen oder Ausdrücken, die Werte zum Einfügen in die Zieltabelle zurückgeben. Ausdrücke dürfen keine EXECUTE-Anweisung enthalten.

<search_condition>

Hiermit werden die Suchbedingungen zum Angeben von <merge_search_condition> oder <clause_search_condition> angegeben. Weitere Informationen zu den Argumenten für diese Klausel finden Sie unter Suchbedingung (Transact-SQL).

Hinweise

Mindestens eine der drei MATCHED-Klauseln muss angegeben werden, dies kann jedoch in beliebiger Reihenfolge erfolgen. Eine Variable in derselben MATCHED-Klausel kann nicht mehr als einmal aktualisiert werden.

Jede Einfüge-, Update- oder Löschaktion, die in der Zieltabelle durch die MERGE-Anweisung angegeben wird, ist durch alle für die Tabelle definierten Beschränkungen eingeschränkt, einschließlich aller kaskadierenden referenziellen Integritätsbeschränkungen. Wenn IGNORE_DUP_KEY für alle eindeutigen Indizes in der Zieltabelle auf ON festgelegt ist, ignoriert MERGE diese Einstellung.

Die MERGE-Anweisung erfordert ein Semikolon (;) als Abschlusszeichen für die Anweisung. Wenn eine MERGE-Anweisung ohne das Abschlusszeichen ausgeführt wird, wird der Fehler 10713 generiert.

Bei Verwendung nach MERGE gibt @@ROWCOUNT (Transact-SQL) die Gesamtanzahl der eingefügten, aktualisierten und gelöschten Zeilen an den Client zurück.

MERGE ist ein vollständig reserviertes Schlüsselwort, wenn der Kompatibilitätsgrad der Datenbank auf 100 oder höher festgelegt ist. Die MERGE-Anweisung ist bei einem Kompatibilitätsgrad von sowohl 90 als auch 100 verfügbar. Bei einem Kompatibilitätsgrad von 90 ist das Schlüsselwort allerdings nicht vollständig reserviert.

-- Case 1
SELECT a.name,
    c.distribution_policy_desc,
    b.type
FROM sys.tables a
INNER JOIN sys.indexes b
    ON a.object_id = b.object_id
INNER JOIN sys.pdw_table_distribution_properties c
    ON a.object_id = c.object_id
WHERE b.type = 2
    AND c.distribution_policy_desc = 'HASH';

-- Subject to Case 2, if distribution key value is updated in MERGE statement
SELECT a.name,
    c.distribution_policy_desc
FROM sys.tables a
INNER JOIN sys.pdw_table_distribution_properties c
    ON a.object_id = c.object_id
WHERE c.distribution_policy_desc = 'HASH';

IF object_id('[check_table_1]', 'U') IS NOT NULL
    DROP TABLE [check_table_1]
GO

IF object_id('[check_table_2]', 'U') IS NOT NULL
    DROP TABLE [check_table_2]
GO

CREATE TABLE [check_table_1]
    WITH (DISTRIBUTION = ROUND_ROBIN) AS

SELECT <DISTRIBUTION_COLUMN> AS x
FROM <MERGE_TABLE>
GROUP BY <DISTRIBUTION_COLUMN>;
GO

CREATE TABLE [check_table_2]
    WITH (DISTRIBUTION = HASH (x)) AS

SELECT x
FROM [check_table_1];
GO

IF NOT EXISTS (
        SELECT TOP 1 *
        FROM (
            SELECT <DISTRIBUTION_COLUMN> AS x
            FROM <MERGE_TABLE>

            EXCEPT

            SELECT x
            FROM [check_table_2]
            ) AS tmp
        )
    SELECT 'no need for repair' AS result
ELSE
    SELECT 'needs repair' AS result
GO

IF object_id('[check_table_1]', 'U') IS NOT NULL
    DROP TABLE [check_table_1]
GO

IF object_id('[check_table_2]', 'U') IS NOT NULL
    DROP TABLE [check_table_2]
GO

IF object_id('[repair_table_temp]', 'U') IS NOT NULL
    DROP TABLE [repair_table_temp];
GO

IF object_id('[repair_table]', 'U') IS NOT NULL
    DROP TABLE [repair_table];
GO

CREATE TABLE [repair_table_temp]
    WITH (DISTRIBUTION = ROUND_ROBIN) AS

SELECT *
FROM <MERGE_TABLE>;
GO

-- [repair_table] will hold the repaired table generated from <MERGE_TABLE>
CREATE TABLE [repair_table]
    WITH (DISTRIBUTION = HASH (<DISTRIBUTION_COLUMN>)) AS

SELECT *
FROM [repair_table_temp];
GO

IF object_id('[repair_table_temp]', 'U') IS NOT NULL
    DROP TABLE [repair_table_temp];
GO

Triggerimplementierung

Für jeden Einfüge-, Update- oder Löschvorgang, der in der MERGE-Anweisung angegeben ist, löst SQL Server alle entsprechenden AFTER-Trigger aus, die in der Zieltabelle definiert sind, gewährleistet jedoch nicht, für welche Aktion Trigger zuerst oder zuletzt ausgelöst werden. Trigger, die für dieselbe Aktion definiert sind, halten sich an die von Ihnen angegebene Reihenfolge. Weitere Informationen zum Festlegen der Reihenfolge beim Auslösen von Triggern finden Sie unter Angeben des ersten und des letzten Triggers.

Wenn in der Zieltabelle ein aktivierter INSTEAD OF-Trigger für einen Einfüge-, Update- oder Löschvorgang definiert ist, der durch eine MERGE-Anweisung ausgeführt wird, muss sie einen aktivierten INSTEAD OF-Trigger für alle in der MERGE-Anweisung angegebenen Aktionen enthalten.

Wenn für target_table ein INSTEAD OF UPDATE-Trigger oder INSTEAD OF DELETE-Trigger definiert ist, werden die Update- oder Löschvorgänge nicht ausgeführt. Stattdessen werden die Trigger ausgelöst, und die inserted- und deleted-Tabelle werden entsprechend aufgefüllt.

Wenn für target_table der INSTEAD OF INSERT-Trigger definiert ist, wird der Einfügevorgang nicht ausgeführt. Stattdessen wird die Tabelle entsprechend aufgefüllt.

Berechtigungen

Erfordert die SELECT-Berechtigung für die Quelltabelle und die INSERT-, UPDATE- oder DELETE-Berechtigung für die Zieltabelle. Weitere Informationen finden Sie im Abschnitt „Berechtigungen“ in den Artikeln zu SELECT, INSERT, UPDATE und DELETE.

Bewährte Methoden zum Indizieren

Mit der MERGE-Anweisung können Sie die einzelnen DML-Anweisungen durch eine einzelne Anweisung ersetzen. Auf diese Weise können Sie die Abfrageleistung verbessern, da die Vorgänge innerhalb einer einzelnen Anweisung ausgeführt und so die Anzahl der Verarbeitungsvorgänge für die Daten in der Quell- und Zieltabelle minimiert werden. Leistungssteigerungen sind jedoch von richtigen Indizes, Joins und anderen Fragen abhängig.

Zur Leistungsverbesserung der MERGE-Anweisung werden die folgenden Indexrichtlinien empfohlen:

• Erstellen Sie Indizes, um den Join zwischen Quelle und Ziel von MERGE zu vereinfachen:
  • Erstellen Sie einen Index für die Verknüpfungsspalten in der Quelltabelle mit Schlüsseln, die die Verknüpfungslogik für die Zieltabelle abdecken. Wenn möglich, sollte er eindeutig sein.
  • Erstellen Sie außerdem einen Index für die Verknüpfungsspalten in der Zieltabelle. Falls möglich, sollte es sich um einen eindeutigen gruppierten Index handeln.
  • Diese beiden Indizes stellen sicher, dass die Daten in den Tabellen sortiert sind, und Eindeutigkeit unterstützt die Leistung des Vergleichs. Die Abfrageleistung wird verbessert, da der Abfrageoptimierer keine zusätzliche Validierung ausführen muss, um doppelte Zeilen zu suchen und zu aktualisieren, und zusätzliche Sortiervorgänge nicht erforderlich sind.
• Vermeiden Sie Tabellen mit jeglicher Form von Columnstore-Index als Ziel von MERGE-Anweisungen. Wie bei allen UPDATEs-Anweisungen können Sie die Leistung mit Columnstore-Indizes verbessern, indem Sie eine gestagete Rowstoretabelle aktualisieren und dann anstelle von UPDATE oder MERGE eine DELETE- und INSERT-Batchverarbeitung ausführen.

Parallelitätsüberlegungen für MERGE

In Bezug auf das Sperren unterscheidet sich MERGE von einzelnen, aufeinanderfolgenden INSERT-, UPDATE- und DELETE-Anweisungen. MERGE führt weiterhin INSERT-, UPDATE- und DELETE-Vorgänge aus, verwendet jedoch unterschiedliche Sperrmechanismen. Für einige Anwendungsanforderungen kann es effizienter sein, einzelne INSERT-, UPDATE- und DELETE-Anweisungen zu schreiben. Im großen Maßstab kann MERGE zu komplizierten Parallelitätsproblemen führen oder eine erweiterte Problembehandlung erfordern. Planen Sie daher ein, alle MERGE-Anweisungen vor der Bereitstellung in der Produktion gründlich zu testen.

MERGE-Anweisungen sind ein geeigneter Ersatz für einzelne INSERT-, UPDATE- und DELETE-Vorgänge in (aber nicht nur) den folgenden Szenarien:

• ETL-Vorgänge mit einer großen Zeilenanzahl werden zu einer Uhrzeit ausgeführt, in der andere gleichzeitige Vorgänge nicht* erwartet werden. Wenn hohe Parallelität zu erwarten ist, kann eine separate INSERT-, UPDATE- und DELETE-Logik besser funktionieren und weniger blockierend sein als eine MERGE-Anweisung.
• Komplexe Vorgänge mit geringer Zeilenanzahl und Transaktionen, die für längere Zeit wahrscheinlich nicht ausgeführt werden.
• Komplexe Vorgänge mit Benutzertabellen, bei denen Indizes so gestaltet werden können, dass sie optimale Ausführungspläne gewährleisten, indem Tabellenscans und Lookups zugunsten von Indexscans oder – im Idealfall – Indexsuchvorgängen vermieden werden.

Weitere Überlegungen zur Parallelität:

• In einigen Szenarien, in denen erwartet wird, dass eindeutige Schlüssel durch MERGE sowohl eingefügt als auch aktualisiert werden, wird durch Angabe von HOLDLOCK verhindert, dass eindeutige Schlüssel verletzt werden. HOLDLOCK ist ein Synonym für die Transaktionsisolationsstufe SERIALIZABLE, die es anderen gleichzeitigen Transaktionen nicht erlaubt, Daten zu ändern, die diese Transaktion gelesen hat. SERIALIZABLE ist die sicherste Isolationsstufe, sorgt aber für die geringste Parallelität mit anderen Transaktionen, die Sperren auf Datenbereiche beibehält, um zu verhindern, dass Phantomzeilen eingefügt oder aktualisiert werden, während Lesevorgänge in Bearbeitung sind. Weitere Informationen zu HOLDLOCK finden Sie unter Hinweise and FESTLEGEN DER TRANSAKTIONSISOLATIONSSTUFE (Transact-SQL).

Bewährte Methoden für JOIN

Zur Leistungsverbesserung der MERGE-Anweisung und Sicherstellung richtiger Ergebnisse werden die folgenden Joinrichtlinien empfohlen:

• Geben Sie in der ON <merge_search_condition>-Klausel nur Suchbedingungen an, die die Kriterien für den Vergleich von Daten in den Quell- und Zieltabellen bestimmen. Geben Sie also nur Spalten aus der Zieltabelle an, die mit den entsprechenden Spalten der Quelltabelle verglichen werden.
• Fügen Sie keine Vergleiche mit anderen Werten, z. B. einer Konstante, ein.

Zum Filtern von Zeilen aus der Quell- oder Zieltabelle verwenden Sie eine der folgenden Methoden:

• Geben Sie die Suchbedingung für die Zeilenfilterung in der entsprechenden WHEN-Klausel an. Beispiel: WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT....
• Definieren Sie in der Quelle oder im Ziel eine Sicht, die die gefilterten Zeilen zurückgibt, und verweisen Sie auf die Sicht als Quell- oder Zieltabelle. Wenn die Sicht für die Zieltabelle definiert ist, müssen alle Aktionen für die Tabelle die Bedingungen zum Aktualisieren von Sichten erfüllen. Weitere Informationen zum Aktualisieren von Daten mithilfe von Sichten finden Sie unter „Ändern von Daten über eine Sicht“.
• Mit der WITH <common table expression>-Klausel können Sie Zeilen aus den Quell- oder Zieltabellen filtern. Diese Methode ähnelt dem Angeben zusätzlicher Suchkriterien in der ON-Klausel und kann zu falschen Ergebnissen führen. Es wird empfohlen, die Verwendung dieser Methode zu vermeiden oder diese vor der Implementierung gründlich zu testen.

Der Joinvorgang in der MERGE-Anweisung wird auf dieselbe Weise optimiert wie ein Join in einer SELECT-Anweisung. Das heißt, beim Verarbeiten von Joins durch SQL Server wählt der Abfrageoptimierer (aus verschiedenen Möglichkeiten) die effizienteste Methode aus. Wenn Quelle und Ziel von ähnlicher Größe sind und die zuvor beschriebenen Indizierungsrichtlinien auf die Quell- und Zieltabellen angewendet werden, bildet ein Merge Join-Operator den effizientesten Abfrageplan. Das liegt daran, dass beide Tabellen einmal durchsucht werden und anschließend keine Notwendigkeit vorliegt, die Daten zu sortieren. Wenn die Quelltabelle kleiner als die Zieltabelle ist, ist ein Nested Loops-Operator vorzuziehen.

Sie können die Verwendung einer bestimmten Join erzwingen, indem Sie in der MERGE-Anweisung die OPTION (<query_hint>)-Klausel angeben. Es wird empfohlen, als Abfragehinweis für MERGE-Anweisungen nicht den Hashjoin zu verwenden, da von diesem Jointyp keine Indizes verwendet werden.

Bewährte Methoden für die Parametrisierung

Wenn eine der SELECT-, INSERT-, UPDATE- oder DELETE-Anweisungen ohne Parameter ausgeführt wird, kann der SQL Server-Abfrageoptimierer die Anweisung intern parametrisieren. Dies bedeutet, dass alle eventuell in der Abfrage enthaltenen Literalwerte durch Parameter ersetzt werden. Beispielsweise kann die Anweisung INSERT dbo.MyTable (Col1, Col2) VALUES (1, 10) intern als INSERT dbo.MyTable (Col1, Col2) VALUES (@p1, @p2) implementiert werden. Dieser als einfache Parametrisierung bezeichnete Vorgang erhöht die Wahrscheinlichkeit, dass die relationale Engine neue SQL-Anweisungen vorhandenen, zuvor kompilierten Ausführungsplänen zuordnet. Möglicherweise wird die Abfrageleistung verbessert, da die Häufigkeit der Abfragekompilierungen und Neukompilierungen reduziert wird. Die einfache Parametrisierung wird vom Abfrageoptimierer nicht auf MERGE-Anweisungen angewendet. Daher ist die Leistung bei der Ausführung von MERGE-Anweisungen mit Literalwerten nicht so hoch wie bei einzelnen INSERT-, UPDATE- oder DELETE-Anweisungen, da bei jeder Ausführung der MERGE-Anweisung ein neuer Plan kompiliert wird.

Um die Abfrageleistung zu verbessern, werden die folgenden Parametrisierungsrichtlinien empfohlen:

• Parametrisieren Sie alle Literalwerte in der ON <merge_search_condition>-Klausel sowie in den WHEN-Klauseln der MERGE-Anweisung. Beispielsweise können Sie die MERGE-Anweisung in eine gespeicherte Prozedur integrieren und dabei die Literalwerte durch die entsprechenden Eingabeparameter ersetzen.
• Wenn Sie die Anweisung nicht parametrisieren können, erstellen Sie eine Planhinweisliste vom Typ TEMPLATE, und geben Sie in der Planhinweisliste den Abfragehinweis PARAMETERIZATION FORCED an. Weitere Informationen finden Sie unter Angeben des Abfrageparametrisierungsverhaltens mithilfe von Planhinweislisten.
• Wenn MERGE-Anweisungen für die Datenbank häufig ausgeführt werden, empfiehlt es sich möglicherweise, die PARAMETERIZATION-Option für die Datenbank auf FORCED festzulegen. Legen Sie diese Option mit Bedacht fest. Die PARAMETERIZATION-Option ist eine Einstellung auf Datenbankebene und wirkt sich auf die Verarbeitung aller Abfragen für die Datenbank aus. Weitere Informationen finden Sie unter Erzwungene Parametrisierung.
• Als neuere und einfachere Alternative zu Planhinweislisten sollten Sie eine ähnliche Strategie mit Abfragespeicherhinweisen erwägen. Weitere Informationen finden Sie unter Abfragespeicherhinweise.

Bewährte Methoden für die TOP-Klausel

In der MERGE-Anweisung gibt die TOP-Klausel die Anzahl oder den Prozentsatz der Zeilen an, auf die sich das Verknüpfen der Quelltabelle mit der Zieltabelle auswirkt, nachdem Zeilen, auf die keine INSERT-, UPDATE- oder DELETE-Aktion angewendet wird, entfernt wurden. Die TOP-Klausel verringert zudem die Anzahl der verknüpften Zeilen auf den angegebenen Wert, und die INSERT-, UPDATE- oder DELETE-Aktionen werden ungeordnet auf die verbliebenen verknüpften Zeilen angewendet. Dies bedeutet, dass für die Verteilung der Zeilen auf die in den WHEN-Klauseln definierten Aktionen keine bestimmte Reihenfolge gilt. Wenn beispielsweise TOP (10) angegeben wird, sind 10 Zeilen betroffen. Von diesen Zeilen können sieben aktualisiert und drei eingefügt werden, oder eine Zeile kann gelöscht, fünf können aktualisiert und vier eingefügt werden usw.

Häufig wird die TOP-Klausel zum batchweisen Ausführen von DML-Vorgängen (Data Manipulation Language, Datenbearbeitungssprache) für eine umfangreiche Tabelle verwendet. Wenn Sie die TOP-Klausel zu diesem Zweck in der MERGE-Anweisung verwenden, müssen Sie sich der folgenden Auswirkungen bewusst sein.

• Die E/A-Leistung ist möglicherweise betroffen.

  Die MERGE-Anweisung führt einen vollständigen Tabellenscan der Quell- und der Zieltabelle aus. Durch Aufteilung des Vorgangs in Batches wird die Anzahl der Schreibvorgänge pro Batch reduziert. Für jeden Batch wird jedoch ein vollständiger Tabellenscan der Quell- und der Zieltabelle ausgeführt. Die daraus resultierende Leseaktivität kann die Leistung der Abfrage und anderer gleichzeitiger Aktivitäten in den Tabellen beeinträchtigen.

• Es können falsche Ergebnisse auftreten.

  Es sollte unbedingt sichergestellt werden, dass alle aufeinander folgenden Batches neuen Zeilen zugeordnet sind, andernfalls kann ein unerwünschtes Verhalten auftreten, z. B. das Einfügen doppelter Zeilen in die Zieltabelle. Dies kann der Fall sein, wenn die Quelltabelle eine Zeile enthält, die nicht im Zielbatch, aber in der Zieltabelle insgesamt enthalten war. So stellen Sie die Richtigkeit der Ergebnisse sicher

  • Bestimmen Sie mithilfe der ON-Klausel die Quellzeilen, die sich auf vorhandene Zielzeilen auswirken bzw. tatsächlich neu sind.
  • Bestimmen Sie mithilfe einer zusätzlichen Bedingung in der WHEN MATCHED-Klausel, ob die Zielzeile bereits in einem früheren Batch aktualisiert wurde.
  • Verwenden Sie eine zusätzliche Bedingung in der WHEN MATCHED-Klausel und SET-Logik, um zu bestätigen, dass die gleiche Zeile nicht zweimal aktualisiert werden kann.

Da die TOP-Klausel erst nach diesen Klauseln angewendet wird, wird bei jeder Ausführung eine Zeile, die tatsächlich keine Entsprechung besitzt, eingefügt, oder es wird eine vorhandene Zeile aktualisiert.

Bewährte Methoden zum Massenladen

Die MERGE-Anweisung kann zum effizienten Massenladen von Daten aus einer Quelldatendatei in eine Zieltabelle verwendet werden, indem die OPENROWSET(BULK...)-Klausel als Tabellenquelle angegeben wird. Dadurch wird die gesamte Datei als einzelner Batch verarbeitet.

Zur Leistungsverbesserung des Massenladevorgangs werden die folgenden Richtlinien empfohlen:

• Erstellen Sie für die Joinspalten in der Zieltabelle einen gruppierten Index.

• Deaktivieren Sie andere nicht eindeutige, nicht gruppierte Indizes für die Zieltabelle während des Massenladevorgangs mit MERGE, und aktivieren Sie sie anschließend. Dies ist üblich und nützlich für nächtliche Massendatenvorgänge.

• Geben Sie mithilfe des ORDER-Hinweises und des UNIQUE-Hinweises in der OPENROWSET(BULK...)-Klausel an, wie die Quelldatendatei sortiert ist.

  Standardmäßig geht der Massenvorgang davon aus, dass die Datendatei nicht sortiert ist. Daher ist es wichtig, dass die Quelldaten anhand des gruppierten Indexes für die Zieltabelle sortiert sind und die Reihenfolge mit dem ORDER-Hinweis angegeben wird, sodass der Abfrageoptimierer einen effizienteren Abfrageplan generieren kann. Hinweise werden zur Laufzeit validiert. Wenn der Datenstrom mit den angegebenen Hinweisen nicht übereinstimmt, wird ein Fehler ausgelöst.

Mit diesen Richtlinien wird sichergestellt, dass die Joinschlüssel eindeutig sind und die Sortierreihenfolge der Daten in der Quelldatei mit der Zieltabelle übereinstimmt. Die Abfrageleistung wird verbessert, da zusätzliche Sortiervorgänge nicht erforderlich sind und keine nicht benötigten Datenkopien angefordert werden.

Messen und Diagnostizieren der MERGE-Leistung

Die folgenden Funktionen stehen Ihnen zur Verfügung, um die Leistung von MERGE-Anweisungen zu messen und zu diagnostizieren.

• Geben Sie mithilfe des Zählers merge stmt in der dynamischen Verwaltungssicht sys.dm_exec_query_optimizer_info die Anzahl von Abfrageoptimierungen für MERGE-Anweisungen zurück.
• Geben Sie mithilfe des merge_action_type-Attributs in der dynamischen Verwaltungssicht sys.dm_exec_plan_attributes den Typ des Triggerausführungsplans zurück, der als Ergebnis einer MERGE-Anweisung verwendet wird.
• Erfassen Sie in einer Sitzungen für erweiterte Ereignisse Problembehandlungsdaten für die MERGE-Anweisung auf dieselbe Weise wie für andere DML-Anweisungen (Data Manipulation Language). Weitere Informationen zur Übersicht über erweiterte Ereignisse finden Sie unter Schnellstart: Erweiterte Ereignisse und Verwenden des SSMS XEvent Profiler.

Beispiele

A. Verwenden von MERGE zum Ausführen von INSERT- und UPDATE-Vorgängen für eine Tabelle in einer einzelnen Anweisung

Ein häufiges Szenario ist die Aktualisierung einer oder mehrerer Spalten in einer Tabelle, wenn eine übereinstimmende Zeile vorhanden ist. Anderenfalls, wenn keine übereinstimmende Zeile vorhanden ist, das Einfügen der Daten als neue Zeile. In jedem Szenario übergeben Sie normalerweise Parameter an eine gespeicherte Prozedur, die die entsprechende UPDATE-Anweisung und INSERT-Anweisung enthält. Mit der MERGE-Anweisung können Sie beide Tasks in einer einzelnen Anweisung ausführen. Das folgende Beispiel zeigt eine gespeicherte Prozedur in der AdventureWorks2022-Datenbank, die sowohl eine INSERT-Anweisung als auch eine UPDATE-Anweisung enthält. Anschließend wird die Prozedur so geändert, dass sie die entsprechenden Vorgänge mit einer einzelnen MERGE-Anweisung ausführt.

CREATE PROCEDURE dbo.InsertUnitMeasure @UnitMeasureCode NCHAR(3), @Name NVARCHAR(25)
AS
BEGIN
    SET NOCOUNT ON;

    -- Update the row if it exists.
    UPDATE Production.UnitMeasure
    SET Name = @Name
    WHERE UnitMeasureCode = @UnitMeasureCode

    -- Insert the row if the UPDATE statement failed.
    IF (@@ROWCOUNT = 0)
    BEGIN
        INSERT INTO Production.UnitMeasure (
            UnitMeasureCode,
            Name
        )
        VALUES (@UnitMeasureCode, @Name)
    END
END;
GO

-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Test Value';

SELECT UnitMeasureCode, Name
FROM Production.UnitMeasure
WHERE UnitMeasureCode = 'ABC';
GO

-- Rewrite the procedure to perform the same operations using the
-- MERGE statement.
-- Create a temporary table to hold the updated or inserted values
-- from the OUTPUT clause.
CREATE TABLE #MyTempTable (
    ExistingCode NCHAR(3),
    ExistingName NVARCHAR(50),
    ExistingDate DATETIME,
    ActionTaken NVARCHAR(10),
    NewCode NCHAR(3),
    NewName NVARCHAR(50),
    NewDate DATETIME
);
GO

ALTER PROCEDURE dbo.InsertUnitMeasure @UnitMeasureCode NCHAR(3),
    @Name NVARCHAR(25)
AS
BEGIN
    SET NOCOUNT ON;

    MERGE Production.UnitMeasure AS tgt
    USING (SELECT @UnitMeasureCode, @Name) AS src(UnitMeasureCode, Name)
        ON (tgt.UnitMeasureCode = src.UnitMeasureCode)
    WHEN MATCHED
        THEN
            UPDATE
            SET Name = src.Name
    WHEN NOT MATCHED
        THEN
            INSERT (UnitMeasureCode, Name)
            VALUES (src.UnitMeasureCode, src.Name)
    OUTPUT deleted.*,
        $action,
        inserted.*
    INTO #MyTempTable;
END;
GO

-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'New Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'XYZ', @Name = 'Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Another Test Value';

SELECT * FROM #MyTempTable;

-- Cleanup
DELETE FROM Production.UnitMeasure
WHERE UnitMeasureCode IN ('ABC', 'XYZ');

DROP TABLE #MyTempTable;
GO

CREATE PROCEDURE dbo.InsertUnitMeasure @UnitMeasureCode NCHAR(3),
    @Name NVARCHAR(25)
AS
BEGIN
    SET NOCOUNT ON;

    -- Update the row if it exists.
    UPDATE Production.UnitMeasure
    SET Name = @Name
    WHERE UnitMeasureCode = @UnitMeasureCode

    -- Insert the row if the UPDATE statement failed.
    IF (@@ROWCOUNT = 0)
    BEGIN
        INSERT INTO Production.UnitMeasure (
            UnitMeasureCode,
            Name
        )
        VALUES (@UnitMeasureCode, @Name)
    END
END;
GO

-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Test Value';

SELECT UnitMeasureCode, Name
FROM Production.UnitMeasure
WHERE UnitMeasureCode = 'ABC';
GO

-- Rewrite the procedure to perform the same operations using the
-- MERGE statement.
ALTER PROCEDURE dbo.InsertUnitMeasure @UnitMeasureCode NCHAR(3),
    @Name NVARCHAR(25)
AS
BEGIN
    SET NOCOUNT ON;

    MERGE Production.UnitMeasure AS tgt
    USING (
        SELECT @UnitMeasureCode,
            @Name
        ) AS src(UnitMeasureCode, Name)
        ON (tgt.UnitMeasureCode = src.UnitMeasureCode)
    WHEN MATCHED
        THEN
            UPDATE SET Name = src.Name
    WHEN NOT MATCHED
        THEN
            INSERT (UnitMeasureCode, Name)
            VALUES (src.UnitMeasureCode, src.Name);
END;
GO

-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'New Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'XYZ', @Name = 'Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Another Test Value';

-- Cleanup
DELETE FROM Production.UnitMeasure
WHERE UnitMeasureCode IN ('ABC', 'XYZ');
GO

B. Verwenden von MERGE zum Ausführen von UPDATE- und DELETE-Operationen für eine Tabelle in einer einzelnen Anweisung

Im folgenden Beispiel wird die Tabelle ProductInventory in der AdventureWorks2022-Beispieldatenbank täglich mit MERGE aktualisiert. Dies erfolgt auf der Grundlage der in der Tabelle SalesOrderDetail verarbeiteten Bestellungen. Die Quantity-Spalte der ProductInventory-Tabelle wird aktualisiert, indem die Anzahl der täglich aufgegebenen Bestellungen für die einzelnen Produkte in der SalesOrderDetail-Tabelle subtrahiert wird. Wenn die Anzahl der Bestellungen für ein Produkt dazu führt, dass der Produktbestand auf oder unter 0 (null) fällt, wird die Zeile für dieses Produkt aus der ProductInventory-Tabelle gelöscht.

CREATE PROCEDURE Production.usp_UpdateInventory @OrderDate DATETIME
AS
MERGE Production.ProductInventory AS tgt
USING (
    SELECT ProductID,
        SUM(OrderQty)
    FROM Sales.SalesOrderDetail AS sod
    INNER JOIN Sales.SalesOrderHeader AS soh
        ON sod.SalesOrderID = soh.SalesOrderID
            AND soh.OrderDate = @OrderDate
    GROUP BY ProductID
    ) AS src(ProductID, OrderQty)
    ON (tgt.ProductID = src.ProductID)
WHEN MATCHED
    AND tgt.Quantity - src.OrderQty <= 0
    THEN
        DELETE
WHEN MATCHED
    THEN
        UPDATE
        SET tgt.Quantity = tgt.Quantity - src.OrderQty,
            tgt.ModifiedDate = GETDATE()
OUTPUT $action,
    Inserted.ProductID,
    Inserted.Quantity,
    Inserted.ModifiedDate,
    Deleted.ProductID,
    Deleted.Quantity,
    Deleted.ModifiedDate;
GO

EXECUTE Production.usp_UpdateInventory '20030501';

CREATE PROCEDURE Production.usp_UpdateInventory @OrderDate DATETIME
AS
MERGE Production.ProductInventory AS tgt
USING (
    SELECT ProductID,
        SUM(OrderQty)
    FROM Sales.SalesOrderDetail AS sod
    INNER JOIN Sales.SalesOrderHeader AS soh
        ON sod.SalesOrderID = soh.SalesOrderID
            AND soh.OrderDate = @OrderDate
    GROUP BY ProductID
    ) AS src(ProductID, OrderQty)
    ON (tgt.ProductID = src.ProductID)
WHEN MATCHED
    AND tgt.Quantity - src.OrderQty <= 0
    THEN
        DELETE
WHEN MATCHED
    THEN
        UPDATE
        SET tgt.Quantity = tgt.Quantity - src.OrderQty,
            tgt.ModifiedDate = GETDATE();
GO

EXECUTE Production.usp_UpdateInventory '20030501';

C. Verwenden von MERGE zum Ausführen von UPDATE- und INSERT-Vorgängen für eine Zieltabelle unter Verwendung einer abgeleiteten Quelltabelle

Im folgenden Beispiel wird die Tabelle SalesReason in der AdventureWorks2022-Datenbank durch das Aktualisieren oder Einfügen von Zeilen mithilfe von MERGE geändert.

-- Create a temporary table variable to hold the output actions.
DECLARE @SummaryOfChanges TABLE (Change VARCHAR(20));

MERGE INTO Sales.SalesReason AS tgt
USING (
    VALUES ('Recommendation', 'Other'),
        ('Review', 'Marketing'),
        ('Internet', 'Promotion')
    ) AS src(NewName, NewReasonType)
    ON tgt.Name = src.NewName
WHEN MATCHED
    THEN
        UPDATE
        SET ReasonType = src.NewReasonType
WHEN NOT MATCHED BY TARGET
    THEN
        INSERT (Name, ReasonType)
        VALUES (NewName, NewReasonType)
OUTPUT $action
INTO @SummaryOfChanges;

-- Query the results of the table variable.
SELECT Change,
    COUNT(*) AS CountPerChange
FROM @SummaryOfChanges
GROUP BY Change;

MERGE INTO Sales.SalesReason AS tgt
USING (
    SELECT 'Recommendation', 'Other'
    UNION ALL    
    SELECT 'Review', 'Marketing'
    UNION ALL
    SELECT 'Internet', 'Promotion'
    ) AS src(NewName, NewReasonType)
    ON tgt.Name = src.NewName
WHEN MATCHED
    THEN
        UPDATE SET ReasonType = src.NewReasonType
WHEN NOT MATCHED BY TARGET
    THEN
        INSERT (Name, ReasonType)
        VALUES (NewName, NewReasonType);

CREATE TABLE Production.UpdatedInventory (
    ProductID INT NOT NULL,
    LocationID INT,
    NewQty INT,
    PreviousQty INT,
    CONSTRAINT PK_Inventory PRIMARY KEY CLUSTERED (
        ProductID,
        LocationID
        )
    );
GO

INSERT INTO Production.UpdatedInventory
SELECT ProductID, LocationID, NewQty, PreviousQty
FROM (
    MERGE Production.ProductInventory AS pi
    USING (
        SELECT ProductID, SUM(OrderQty)
        FROM Sales.SalesOrderDetail AS sod
        INNER JOIN Sales.SalesOrderHeader AS soh
            ON sod.SalesOrderID = soh.SalesOrderID
                AND soh.OrderDate BETWEEN '20030701'
                    AND '20030731'
        GROUP BY ProductID
        ) AS src(ProductID, OrderQty)
        ON pi.ProductID = src.ProductID
    WHEN MATCHED
        AND pi.Quantity - src.OrderQty >= 0
        THEN
            UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
    WHEN MATCHED
        AND pi.Quantity - src.OrderQty <= 0
        THEN
            DELETE
    OUTPUT $action,
        Inserted.ProductID,
        Inserted.LocationID,
        Inserted.Quantity AS NewQty,
        Deleted.Quantity AS PreviousQty
    ) AS Changes(Action, ProductID, LocationID, NewQty, PreviousQty)
WHERE Action = 'UPDATE';
GO

-- CREATE node and edge tables
CREATE TABLE Person
(
    ID INTEGER PRIMARY KEY,
    PersonName VARCHAR(100)
)
AS NODE
GO

CREATE TABLE City
(
    ID INTEGER PRIMARY KEY,
    CityName VARCHAR(100),
    StateName VARCHAR(100)
)
AS NODE
GO

CREATE TABLE livesIn
(
    StreetAddress VARCHAR(100)
)
AS EDGE
GO

-- INSERT some test data into node and edge tables
INSERT INTO Person VALUES (1, 'Ron'), (2, 'David'), (3, 'Nancy')
GO

INSERT INTO City VALUES (1, 'Redmond', 'Washington'), (2, 'Seattle', 'Washington')
GO

INSERT livesIn SELECT P.$node_id, C.$node_id, c
FROM Person P, City C, (values (1,1, '123 Avenue'), (2,2,'Main Street')) v(a,b,c)
WHERE P.id = a AND C.id = b
GO

-- Use MERGE to update/insert edge data
CREATE OR ALTER PROCEDURE mergeEdge
    @PersonId integer,
    @CityId integer,
    @StreetAddress varchar(100)
AS
BEGIN
    MERGE livesIn
        USING ((SELECT @PersonId, @CityId, @StreetAddress) AS T (PersonId, CityId, StreetAddress)
                JOIN Person ON T.PersonId = Person.ID
                JOIN City ON T.CityId = City.ID)
        ON MATCH (Person-(livesIn)->City)
    WHEN MATCHED THEN
        UPDATE SET StreetAddress = @StreetAddress
    WHEN NOT MATCHED THEN
        INSERT ($from_id, $to_id, StreetAddress)
        VALUES (Person.$node_id, City.$node_id, @StreetAddress) ;
END
GO

-- Following will insert a new edge in the livesIn edge table
EXEC mergeEdge 3, 2, '4444th Avenue'
GO

-- Following will update the StreetAddress on the edge that connects Ron to Redmond
EXEC mergeEdge 1, 1, '321 Avenue'
GO

-- Verify that all the address were added/updated correctly
SELECT PersonName, CityName, StreetAddress
FROM Person , City , livesIn
WHERE MATCH(Person-(livesIn)->city)
GO

Zugehöriger Inhalt