MERGE (Transact-SQL)MERGE (Transact-SQL)

GILT FÜR: jaSQL ServerjaAzure SQL-DatenbankneinAzure SQL Data Warehouse neinParallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Führt Einfüge-, Aktualisierungs- oder Löschvorgänge in einer Zieltabelle anhand der Ergebnisse eines Joins mit einer Quelltabelle aus.Runs insert, update, or delete operations on a target table from the results of a join with a source table. 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.For example, synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.

Leistungstipp: Das für die MERGE-Anweisung beschriebene bedingte Verhalten funktioniert am besten, wenn die beiden Tabellen eine komplexe Mischung aus übereinstimmenden Eigenschaften aufweisen.Performance Tip: The conditional behavior described for the MERGE statement works best when the two tables have a complex mixture of matching characteristics. Beispielsweise das Einfügen einer Zeile, wenn sie nicht vorhanden ist, oder das Aktualisieren der Zeile, wenn sie übereinstimmt.For example, inserting a row if it doesn't exist, or updating a row if it matches. Wenn Sie eine Tabelle einfach nur basierend auf den Zeilen einer anderen Tabelle aktualisieren, verbessern Sie mit den grundlegenden INSERT-, UPDATE- und DELETE-Anweisungen Leistung und Skalierbarkeit.When simply updating one table based on the rows of another table, improve the performance and scalability with basic INSERT, UPDATE, and DELETE statements. Beispiel:For example:

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);  

Themenlinksymbol Transact-SQL-SyntaxkonventionenTopic link icon Transact-SQL Syntax Conventions

SyntaxSyntax

[ WITH <common_table_expression> [,...n] ]  
MERGE
    [ TOP ( expression ) [ PERCENT ] ]
    [ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]  
    USING <table_source>
    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 . ]  
  target_table  
}  
  
<merge_hint>::=  
{  
    { [ <table_hint_limited> [ ,...n ] ]  
    [ [ , ] INDEX ( index_val [ ,...n ] ) ] }  
}  
  
<table_source> ::=
{  
    table_or_view_name [ [ AS ] table_alias ] [ <tablesample_clause> ]
        [ WITH ( table_hint [ [ , ]...n ] ) ]
  | rowset_function [ [ AS ] table_alias ]
        [ ( bulk_column_alias [ ,...n ] ) ]
  | user_defined_function [ [ AS ] table_alias ]  
  | OPENXML <openxml_clause>
  | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]
  | <joined_table>
  | <pivoted_table>
  | <unpivoted_table>
}  
  
<merge_search_condition> ::=  
    <search_condition>  
  
<merge_matched>::=  
    { UPDATE SET <set_clause> | DELETE }  
  
<set_clause>::=  
SET  
  { column_name = { expression | DEFAULT | NULL }  
  | { udt_column_name.{ { property_name = expression  
                        | field_name = expression }  
                        | method_name ( argument [ ,...n ] ) }  
    }  
  | column_name { .WRITE ( expression , @Offset , @Length ) }  
  | @variable = expression  
  | @variable = column = expression  
  | column_name { += | -= | *= | /= | %= | &= | ^= | |= } expression  
  | @variable { += | -= | *= | /= | %= | &= | ^= | |= } expression  
  | @variable = column { += | -= | *= | /= | %= | &= | ^= | |= } expression  
  } [ ,...n ]
  
<merge_not_matched>::=  
{  
    INSERT [ ( column_list ) ]
        { VALUES ( values_list )  
        | DEFAULT VALUES }  
}  
  
<clause_search_condition> ::=  
    <search_condition>  
  
<search condition> ::=  
    MATCH(<graph_search_pattern>) | <search_condition_without_match> | <search_condition> AND <search_condition>

<search_condition_without_match> ::=
    { [ NOT ] <predicate> | ( <search_condition_without_match> )
    [ { AND | OR } [ NOT ] { <predicate> | ( <search_condition_without_match> ) } ]
[ ,...n ]  

<predicate> ::=
    { expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } expression
    | string_expression [ NOT ] LIKE string_expression
  [ ESCAPE 'escape_character' ]
    | expression [ NOT ] BETWEEN expression AND expression
    | expression IS [ NOT ] NULL
    | CONTAINS
  ( { column | * } , '< contains_search_condition >' )
    | FREETEXT ( { column | * } , 'freetext_string' )
    | expression [ NOT ] IN ( subquery | expression [ ,...n ] )
    | expression { = | < > | ! = | > | > = | ! > | < | < = | ! < }
  { ALL | SOME | ANY} ( subquery )
    | EXISTS ( subquery ) }

<graph_search_pattern> ::=
    { <node_alias> {
                      { <-( <edge_alias> )- }
                    | { -( <edge_alias> )-> }
                    <node_alias>
                   }
    }
  
<node_alias> ::=
    node_table_name | node_table_alias

<edge_alias> ::=
    edge_table_name | edge_table_alias

<output_clause>::=  
{  
    [ OUTPUT <dml_select_list> INTO { @table_variable | output_table }  
        [ (column_list) ] ]  
    [ OUTPUT <dml_select_list> ]  
}  
  
<dml_select_list>::=  
    { <column_name> | scalar_expression }
        [ [AS] column_alias_identifier ] [ ,...n ]  
  
<column_name> ::=  
    { DELETED | INSERTED | from_table_name } . { * | column_name }  
    | $action  

ArgumenteArguments

WITH <common_table_expression>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.Specifies the temporary named result set or view, also known as common table expression, that's defined within the scope of the MERGE statement. Das Resultset wird aus einer einfachen Abfrage abgeleitet. Die MERGE-Anweisung verweist auf dieses Resultset.The result set derives from a simple query and is referenced by the MERGE statement. Weitere Informationen finden Sie unter WITH common_table_expression (Transact-SQL).For more information, see WITH common_table_expression (Transact-SQL).

TOP ( expression ) [ PERCENT ]TOP ( expression ) [ PERCENT ]
Gibt die Anzahl oder den Prozentsatz der betroffenen Zeilen an.Specifies the number or percentage of affected rows. expression kann eine Anzahl oder ein Prozentsatz der Zeilen sein.expression can be either a number or a percentage of the rows. Die Zeilen, auf die im TOP-Ausdruck verwiesen wird, sind nicht auf bestimmte Weise angeordnet.The rows referenced in the TOP expression are not arranged in any order. Weitere Informationen finden Sie unter TOP (Transact-SQL).For more information, see TOP (Transact-SQL).

Die TOP-Klausel wird angewendet, nachdem die gesamte Quelltabelle und die gesamte Zieltabelle verknüpft und die nicht für eine der Aktionen INSERT, UPDATE oder DELETE in Frage kommenden verknüpften Zeilen gelöscht wurden.The TOP clause applies after the entire source table and the entire target table join and the joined rows that don't qualify for an insert, update, or delete action are removed. Die TOP-Klausel reduziert die Anzahl der verknüpften Zeilen mit dem angegebenen Wert noch weiter.The TOP clause further reduces the number of joined rows to the specified value. Die INSERT-, UPDATE- oder DELETE-Aktionen gelten in ungeordneter Weise für die verbliebenen verknüpften Zeilen.The insert, update, or delete actions apply to the remaining joined rows in an unordered way. Dies bedeutet, dass für die Verteilung der Zeilen auf die in den WHEN-Klauseln definierten Aktionen keine bestimmte Reihenfolge gilt.That is, there's no order in which the rows are distributed among the actions defined in the WHEN clauses. Angeben von TOP (10) betrifft z.B. 10 Zeilen.For example, specifying TOP (10) affects 10 rows. Von diesen Zeilen können 7 aktualisiert und 3 eingefügt werden, oder 1 Zeile kann gelöscht, 5 können aktualisiert und 4 eingefügt werden usw.Of these rows, 7 may be updated and 3 inserted, or 1 may be deleted, 5 updated, and 4 inserted, and so on.

Da die MERGE-Anweisung einen vollständigen Tabellenscan der Quell- und der Zieltabelle ausführt, kann die E/A-Leistung beeinträchtigt werden, wenn mit der TOP-Klausel eine große Tabelle durch Erstellen mehrerer Batches geändert wird.Because the MERGE statement does a full table scan of both the source and target tables, I/O performance is sometimes affected when using the TOP clause to modify a large table by creating multiple batches. In diesem Szenario muss unbedingt sichergestellt werden, dass alle aufeinanderfolgenden Batches auf neue Zeilen ausgerichtet sind.In this scenario, it's important to ensure that all successive batches target new rows.

database_namedatabase_name
Der Name der Datenbank, in der sich target_table befindet.The name of the database in which target_table is located.

schema_nameschema_name
Der Namen des Schemas, zu dem die Tabelle target_table gehört.The name of the schema to which target_table belongs.

target_tabletarget_table
Die Tabelle oder Sicht, mit der die Datenzeilen aus <table_source> basierend auf <clause_search_condition> abgeglichen werden.The table or view against which the data rows from <table_source> are matched based on <clause_search_condition>. target_table ist das Ziel aller Einfüge-, Update- oder Löschvorgänge, die durch die WHEN-Klauseln der MERGE-Anweisung angegeben werden.target_table is the target of any insert, update, or delete operations specified by the WHEN clauses of the MERGE statement.

Wenn target_table eine Sicht ist, müssen alle Aktionen für die Tabelle die Bedingungen zum Aktualisieren von Sichten erfüllen.If target_table is a view, any actions against it must satisfy the conditions for updating views. Weitere Informationen finden Sie unter Modify Data Through a View (Ändern von Daten über eine Sicht).For more information, see Modify Data Through a View.

target_table darf keine Remotetabelle sein.target_table can't be a remote table. Für target_table dürfen keine Regeln definiert sein.target_table can't have any rules defined on it.

[ AS ] table_alias[ AS ] table_alias
Ein alternativer Name, über den auf eine Tabelle verwiesen wird.An alternative name to reference a table.

USING <table_source>USING <table_source>
Gibt die Datenquelle an, die basierend auf <merge_search condition> mit den Datenzeilen in target_table abgeglichen wird.Specifies the data source that's matched with the data rows in target_table based on <merge_search condition>. Das Ergebnis dieser Zuordnung legt die Aktionen fest, die von den WHEN-Klauseln der MERGE-Anweisung ausgeführt werden.The result of this match dictates the actions to take by the WHEN clauses of the MERGE statement. <table_source> kann eine Remotetabelle oder eine abgeleitete Tabelle sein, die auf Remotetabellen zugreift.<table_source> can be a remote table or a derived table that accesses remote tables.

<table_source> kann eine abgeleitete Tabelle sein, die mit dem Tabellenwertkonstruktor von Transact-SQLTransact-SQL eine Tabelle durch Angeben mehrerer Zeilen erstellt.<table_source> can be a derived table that uses the Transact-SQLTransact-SQL table value constructor to construct a table by specifying multiple rows.

Weitere Informationen zur Syntax und zu den Argumenten dieser Klausel finden Sie unter FROM (Transact-SQL).For more information about the syntax and arguments of this clause, see FROM (Transact-SQL).

ON <merge_search_condition>ON <merge_search_condition>
Gibt die Bedingungen an, unter denen <table_source> mit target_table verknüpft wird, um Übereinstimmungen zu ermitteln.Specifies the conditions on which <table_source> joins with target_table to determine where they match.

Achtung

Es ist wichtig, dass nur die Spalten aus der Zieltabelle angegeben werden, die für Abgleichszwecke verwendet werden.It's important to specify only the columns from the target table to use for matching purposes. Geben Sie also Spalten aus der Zieltabelle an, die mit der entsprechenden Spalte der Quelltabelle abgeglichen werden.That is, specify columns from the target table that are compared to the corresponding column of the source table. Versuchen Sie nicht, die Abfrageleistung zu optimieren, indem Sie Zeilen in der Zieltabelle in der ON-Klausel herausfiltern, beispielsweise durch Angabe von AND NOT target_table.column_x = value.Don't attempt to improve query performance by filtering out rows in the target table in the ON clause; for example, such as specifying AND NOT target_table.column_x = value. Dadurch kann es zu unerwarteten und falschen Ergebnissen kommen.Doing so may return unexpected and incorrect results.

WHEN MATCHED THEN <merge_matched>WHEN MATCHED THEN <merge_matched>
Gibt an, 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.Specifies that all rows of *target_table, which match the rows returned by <table_source> ON <merge_search_condition>, and satisfy any additional search condition, are either updated or deleted according to the <merge_matched> clause.

Die MERGE-Anweisung kann höchstens über zwei WHEN MATCHED-Klauseln verfügen.The MERGE statement can have, at most, two WHEN MATCHED clauses. Wenn zwei Klauseln angegeben werden, muss die erste Klausel von einer AND <search_condition>-Klausel begleitet werden.If two clauses are specified, the first clause must be accompanied by an AND <search_condition> clause. Für jede gegebene Zeile wird die zweite WHEN MATCHED-Klausel nur angewendet, wenn die erste nicht angewendet wurde.For any given row, the second WHEN MATCHED clause is only applied if the first isn't. Wenn zwei WHEN MATCHED-Klauseln vorhanden sind, muss die eine eine UPDATE-Aktion und die andere eine DELETE-Aktion angeben.If there are two WHEN MATCHED clauses, one must specify an UPDATE action and one must specify a DELETE action. 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 ServerSQL Server einen Fehler zurück.When UPDATE is specified in the <merge_matched> clause, and more than one row of <table_source> matches a row in target_table based on <merge_search_condition>, SQL ServerSQL Server returns an error. Die MERGE-Anweisung kann dieselbe Zeile nicht mehrmals aktualisieren oder dieselbe Zeile aktualisieren und löschen.The MERGE statement can't update the same row more than once, or update and delete the same row.

WHEN NOT MATCHED [ BY TARGET ] THEN <merge_not_matched>WHEN NOT MATCHED [ BY TARGET ] THEN <merge_not_matched>
Gibt an, 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.Specifies that a row is inserted into target_table for every row returned by <table_source> ON <merge_search_condition> that doesn't match a row in target_table, but satisfies an additional search condition, if present. Die einzufügenden Werte werden durch die <merge_not_matched>-Klausel angegeben.The values to insert are specified by the <merge_not_matched> clause. Die MERGE-Anweisung kann nur über eine WHEN NOT MATCHED [ BY TARGET ]-Klausel verfügen.The MERGE statement can have only one WHEN NOT MATCHED [ BY TARGET ] clause.

WHEN NOT MATCHED BY SOURCE THEN <merge_matched>WHEN NOT MATCHED BY SOURCE THEN <merge_matched>
Gibt an, 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.Specifies that all rows of *target_table, which don't match the rows returned by <table_source> ON <merge_search_condition>, and that satisfy any additional search condition, are updated or deleted according to the <merge_matched> clause.

Die MERGE-Anweisung kann höchstens über zwei WHEN NOT MATCHED BY SOURCE-Klauseln verfügen.The MERGE statement can have at most two WHEN NOT MATCHED BY SOURCE clauses. Wenn zwei Klauseln angegeben werden, muss die erste Klausel von einer AND <clause_search_condition>-Klausel begleitet werden.If two clauses are specified, then the first clause must be accompanied by an AND <clause_search_condition> clause. Für jede gegebene Zeile wird die zweite WHEN NOT MATCHED BY SOURCE-Klausel nur angewendet, wenn die erste nicht angewendet wurde.For any given row, the second WHEN NOT MATCHED BY SOURCE clause is only applied if the first isn't. Wenn zwei WHEN NOT MATCHED BY SOURCE-Klauseln vorhanden sind, muss die eine eine UPDATE-Aktion und die andere eine DELETE-Aktion angeben.If there are two WHEN NOT MATCHED BY SOURCE clauses, then one must specify an UPDATE action and one must specify a DELETE action. In <clause_search_condition> kann nur auf Spalten aus der Zieltabelle verwiesen werden.Only columns from the target table can be referenced in <clause_search_condition>.

Wenn von <table_source> keine Zeilen zurückgegeben werden, kann auf Spalten in der Quelltabelle nicht zugegriffen werden.When no rows are returned by <table_source>, columns in the source table can't be accessed. 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.If the update or delete action specified in the <merge_matched> clause references columns in the source table, error 207 (Invalid column name) is returned. 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.For example, the clause WHEN NOT MATCHED BY SOURCE THEN UPDATE SET TargetTable.Col1 = SourceTable.Col1 may cause the statement to fail because Col1 in the source table is inaccessible.

AND <clause_search_condition>AND <clause_search_condition>
Gibt jede gültige Suchbedingung an.Specifies any valid search condition. Weitere Informationen finden Sie unter Suchbedingung (Transact-SQL).For more information, see Search Condition (Transact-SQL).

<table_hint_limited><table_hint_limited>
Gibt mindestens einen Tabellenhinweis an, der für jeden durch die MERGE-Anweisung ausgeführten Einfüge-, Update- oder Löschvorgang auf die Zieltabelle angewendet wird.Specifies one or more table hints to apply on the target table for each of the insert, update, or delete actions done by the MERGE statement. Das WITH-Schlüsselwort und die Klammern sind erforderlich.The WITH keyword and the parentheses are required.

NOLOCK und READUNCOMMITTED sind nicht zulässig.NOLOCK and READUNCOMMITTED aren't allowed. Weitere Informationen zu Tabellenhinweisen finden Sie unter Tabellenhinweise (Transact-SQL).For more information about table hints, see Table Hints (Transact-SQL).

Das Angeben eines TABLOCK-Hinweises für eine Tabelle, die das Ziel einer INSERT-Anweisung ist, hat dieselbe Wirkung wie das Angeben eines TABLOCKX-Hinweises.Specifying the TABLOCK hint on a table that's the target of an INSERT statement has the same effect as specifying the TABLOCKX hint. Auf die Tabelle wird eine exklusive Sperre angewendet.An exclusive lock is taken on the table. Wenn FORCESEEK angegeben wird, wird der Hinweis auf die implizite Instanz der Zieltabelle angewendet, die mit der Quelltabelle verknüpft ist.When FORCESEEK is specified, it applies to the implicit instance of the target table joined with the source table.

Achtung

Wenn READPAST mit WHEN NOT MATCHED [ BY TARGET ] THEN INSERT angegeben wird, kann dies zu INSERT-Operationen führen, die gegen UNIQUE-Beschränkungen verstoßen.Specifying READPAST with WHEN NOT MATCHED [ BY TARGET ] THEN INSERT may result in INSERT operations that violate UNIQUE constraints.

INDEX ( index_val [ ,...n ] )INDEX ( index_val [ ,...n ] )
Gibt den Namen oder die ID eines oder mehrerer Indizes in der Zieltabelle zum Ausführen eines impliziten Joins mit der Quelltabelle an.Specifies the name or ID of one or more indexes on the target table for doing an implicit join with the source table. Weitere Informationen finden Sie unter Tabellenhinweise (Transact-SQL).For more information, see Table Hints (Transact-SQL).

<OUTPUT_Clause><output_clause>
Gibt ohne bestimmte Reihenfolge eine Zeile für jede Zeile in target_table zurück, die aktualisiert, eingefügt oder gelöscht wird.Returns a row for every row in target_table that's updated, inserted, or deleted, in no particular order. $action kann in der OUTPUT-Klausel angegeben werden.$action can be specified in the output clause. $action ist eine Spalte vom Typ nvarchar(10) , die für jede Zeile einen von drei Werten zurückgibt: „INSERT“, „UPDATE“ oder „DELETE“, je nach der für diese Zeile ausgeführten Aktion.$action is a column of type nvarchar(10) that returns one of three values for each row: 'INSERT', 'UPDATE', or 'DELETE', according to the action done on that row. Weitere Informationen zu den Argumenten dieser Klausel finden Sie unter OUTPUT-Klausel (Transact-SQL).For more information about the arguments of this clause, see OUTPUT Clause (Transact-SQL).

OPTION ( <query_hint> [ ,...n ] )OPTION ( <query_hint> [ ,...n ] )
Gibt an, dass zum Anpassen der Art und Weise, wie die Anweisung durch die Datenbank-Engine verarbeitet wird, Hinweise des Abfrageoptimierers verwendet werden.Specifies that optimizer hints are used to customize the way the Database Engine processes the statement. Weitere Informationen finden Sie unter Abfragehinweise (Transact-SQL).For more information, see Query Hints (Transact-SQL).

<merge_matched><merge_matched>
Gibt die Update- oder Löschaktion an, 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.Specifies the update or delete action that's applied to all rows of target_table that don't match the rows returned by <table_source> ON <merge_search_condition>, and which satisfy any additional search condition.

UPDATE SET <set_clause>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.Specifies the list of column or variable names to update in the target table and the values with which to update them.

Weitere Informationen zu den Argumenten dieser Klausel finden Sie unter UPDATE (Transact-SQL).For more information about the arguments of this clause, see UPDATE (Transact-SQL). Eine Variable auf denselben Wert festzulegen wie eine Spalte wird nicht unterstützt.Setting a variable to the same value as a column isn't supported.

DeleteDELETE
Gibt an, dass die Zeilen, die mit Zeilen in target_table übereinstimmen, gelöscht werden.Specifies that the rows matching rows in target_table are deleted.

<merge_not_matched><merge_not_matched>
Gibt die Werte an, die in die Zieltabelle eingefügt werden sollen.Specifies the values to insert into the target table.

(column_list)(column_list)
Eine Liste mit einer oder mehreren Spalten der Zieltabelle, in die Daten eingefügt werden sollen.A list of one or more columns of the target table in which to insert data. Spalten müssen als einteiliger Name angegeben werden. Andernfalls schlägt die MERGE-Anweisung fehl.Columns must be specified as a single-part name or else the MERGE statement will fail. column_list muss in Klammern eingeschlossen und durch ein Trennzeichen getrennt werden.column_list must be enclosed in parentheses and delimited by commas.

VALUES ( values_list)VALUES ( values_list)
Eine durch Trennzeichen getrennte Liste mit Konstanten, Variablen oder Ausdrücken, die Werte zum Einfügen in die Zieltabelle zurückgeben.A comma-separated list of constants, variables, or expressions that return values to insert into the target table. Ausdrücke dürfen keine EXECUTE-Anweisung enthalten.Expressions can't contain an EXECUTE statement.

DEFAULT VALUESDEFAULT VALUES
Erzwingt, dass die eingefügte Zeile den für jede Spalte definierten Standardwert enthält.Forces the inserted row to contain the default values defined for each column.

Weitere Informationen zu dieser Klausel finden Sie unter INSERT (Transact-SQL).For more information about this clause, see INSERT (Transact-SQL).

<search condition><search condition>
Gibt die Suchbedingungen an, die zum Angeben von <merge_search_condition> oder <clause_search_condition> verwendet werden.Specifies the search conditions to specify <merge_search_condition> or <clause_search_condition>. Weitere Informationen zu den Argumenten für diese Klausel finden Sie unter Suchbedingung (Transact-SQL).For more information about the arguments for this clause, see Search Condition (Transact-SQL).

<Graph-Suchmuster ><graph search pattern>
Gibt das Graph-Vergleichsmuster an.Specifies the graph match pattern. Weitere Informationen zu den Argumenten für diese Klausel finden Sie unter MATCH (Transact-SQL).For more information about the arguments for this clause, see MATCH (Transact-SQL)

RemarksRemarks

Mindestens eine der drei MATCHED-Klauseln muss angegeben werden, dies kann jedoch in beliebiger Reihenfolge erfolgen.At least one of the three MATCHED clauses must be specified, but they can be specified in any order. Eine Variable in derselben MATCHED-Klausel kann nicht mehr als einmal aktualisiert werden.A variable can't be updated more than once in the same MATCHED clause.

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.Any insert, update, or delete action specified on the target table by the MERGE statement are limited by any constraints defined on it, including any cascading referential integrity constraints. Wenn IGNORE_DUP_KEY für alle eindeutigen Indizes in der Zieltabelle auf ON festgelegt ist, ignoriert MERGE diese Einstellung.If IGNORE_DUP_KEY is ON for any unique indexes on the target table, MERGE ignores this setting.

Die MERGE-Anweisung erfordert ein Semikolon (;) als Abschlusszeichen für die Anweisung.The MERGE statement requires a semicolon (;) as a statement terminator. Wenn eine MERGE-Anweisung ohne das Abschlusszeichen ausgeführt wird, wird der Fehler 10713 generiert.Error 10713 is raised when a MERGE statement is run without the terminator.

Bei Verwendung nach MERGE gibt @@ROWCOUNT (Transact-SQL) die Gesamtanzahl der eingefügten, aktualisierten und gelöschten Zeilen an den Client zurück.When used after MERGE, @@ROWCOUNT (Transact-SQL) returns the total number of rows inserted, updated, and deleted to the client.

MERGE ist ein vollständig reserviertes Schlüsselwort, wenn der Kompatibilitätsgrad der Datenbank auf 100 oder höher festgelegt ist.MERGE is a fully reserved keyword when the database compatibility level is set to 100 or higher. 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.The MERGE statement is available under both 90 and 100 database compatibility levels; however, the keyword isn't fully reserved when the database compatibility level is set to 90.

Verwenden Sie die MERGE-Anweisung nicht zusammen mit dem Replikationstyp „Verzögertes Update über eine Warteschlange“.Don't use the MERGE statement when using queued updating replication. MERGE und der Trigger für verzögerte Updates über eine Warteschlange sind nicht kompatibel.The MERGE and queued updating trigger aren't compatible. Ersetzen Sie die MERGE-Anweisung durch eine INSERT- oder UPDATE-Anweisung.Replace the MERGE statement with an insert or an update statement.

TriggerimplementierungTrigger Implementation

Für jeden Einfüge-, Update- oder Löschvorgang, der in der MERGE-Anweisung angegeben ist, löst SQL ServerSQL 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.For every insert, update, or delete action specified in the MERGE statement, SQL ServerSQL Server fires any corresponding AFTER triggers defined on the target table, but doesn't guarantee on which action to fire triggers first or last. Trigger, die für dieselbe Aktion definiert sind, halten sich an die von Ihnen angegebene Reihenfolge.Triggers defined for the same action honor the order you specify. Weitere Informationen zum Festlegen der Reihenfolge beim Auslösen von Triggern finden Sie unter Angeben des ersten und des letzten Triggers.For more information about setting trigger firing order, see Specify First and Last 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.If the target table has an enabled INSTEAD OF trigger defined on it for an insert, update, or delete action done by a MERGE statement, it must have an enabled INSTEAD OF trigger for all of the actions specified in the MERGE statement.

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.If any INSTEAD OF UPDATE or INSTEAD OF DELETE triggers are defined on target_table, the update or delete operations aren't run. Stattdessen werden die Trigger ausgelöst, und die inserted- und deleted-Tabelle werden entsprechend aufgefüllt.Instead, the triggers fire and the inserted and deleted tables then populate accordingly.

Wenn für target_table der INSTEAD OF INSERT-Trigger definiert ist, wird der Einfügevorgang nicht ausgeführt.If any INSTEAD OF INSERT triggers are defined on target_table, the insert operation isn't performed. Stattdessen wird die Tabelle entsprechend aufgefüllt.Instead, the table populates accordingly.

BerechtigungenPermissions

Erfordert die SELECT-Berechtigung für die Quelltabelle und die INSERT-, UPDATE- oder DELETE-Berechtigung für die Zieltabelle.Requires SELECT permission on the source table and INSERT, UPDATE, or DELETE permissions on the target table. Weitere Informationen finden Sie im Abschnitt „Berechtigungen“ in den Artikeln zu SELECT, INSERT, UPDATE und DELETE.For more information, see the Permissions section in the SELECT, INSERT, UPDATE, and DELETE articles.

Optimieren der Leistung von MERGE-AnweisungenOptimizing MERGE Statement Performance

Mit der MERGE-Anweisung können Sie die einzelnen DML-Anweisungen durch eine einzelne Anweisung ersetzen.By using the MERGE statement, you can replace the individual DML statements with a single statement. Auf diese Weise können Sie die Abfrageleistung verbessern, weil die Vorgänge innerhalb einer einzelnen Anweisung ausgeführt werden und so die Anzahl der Verarbeitungsvorgänge für die Daten in der Quell- und Zieltabelle minimiert wird.This can improve query performance because the operations are performed within a single statement, therefore, minimizing the number of times the data in the source and target tables are processed. Leistungssteigerungen sind jedoch von richtigen Indizes, Joins und anderen Faktoren abhängig.However, performance gains depend on having correct indexes, joins, and other considerations in place.

Bewährte Methoden für IndizesIndex Best Practices

Zur Leistungsverbesserung der MERGE-Anweisung werden die folgenden Indexrichtlinien empfohlen:To improve the performance of the MERGE statement, we recommend the following index guidelines:

  • Erstellen Sie einen eindeutigen und umfassenden Index für die Joinspalten der Quelltabelle.Create an index on the join columns in the source table that is unique and covering.
  • Erstellen Sie für die Joinspalten in der Zieltabelle einen eindeutigen gruppierten Index.Create a unique clustered index on the join columns in the target table.

Mit diesen Indizes wird sichergestellt, dass die Joinschlüssel eindeutig und die Daten in den Tabellen sortiert sind.These indexes ensure that the join keys are unique and the data in the tables is sorted. Die Abfrageleistung wird verbessert, weil 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.Query performance is improved because the query optimizer does not need to perform extra validation processing to locate and update duplicate rows and additional sort operations are not necessary.

Bewährte Methoden für JOINJOIN Best Practices

Zur Leistungsverbesserung der MERGE-Anweisung und zur Sicherstellung richtiger Ergebnisse werden die folgenden Joinrichtlinien empfohlen:To improve the performance of the MERGE statement and ensure correct results are obtained, we recommend the following join guidelines:

  • 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.Specify only search conditions in the ON <merge_search_condition> clause that determine the criteria for matching data in the source and target tables. Geben Sie also nur Spalten aus der Zieltabelle an, die mit den entsprechenden Spalten der Quelltabelle verglichen werden.That is, specify only columns from the target table that are compared to the corresponding columns of the source table.
  • Fügen Sie keine Vergleiche mit anderen Werten, z. B. einer Konstante, ein.Do not include comparisons to other values such as a constant.

Verwenden Sie zum Filtern von Zeilen aus den Quell- oder Zieltabellen eine der folgenden Methoden.To filter out rows from the source or target tables, use one of the following methods.

  • Geben Sie die Suchbedingung für die Zeilenfilterung in der entsprechenden WHEN-Klausel an.Specify the search condition for row filtering in the appropriate WHEN clause. Beispiel: WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT....For example, WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT....
  • Definieren Sie für die Quelle oder das Ziel eine Sicht, die die gefilterten Zeilen zurückgibt, und verweisen Sie auf die Sicht als Quell- oder Zieltabelle.Define a view on the source or target that returns the filtered rows and reference the view as the source or target table. 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.If the view is defined on the target table, any actions against it must satisfy the conditions for updating views. Weitere Informationen zum Aktualisieren von Daten mithilfe von Sichten finden Sie unter „Ändern von Daten über eine Sicht“.For more information about updating data by using a view, see Modifying Data Through a View.
  • Mit der WITH <common table expression>-Klausel können Sie Zeilen aus den Quell- oder Zieltabellen filtern.Use the WITH <common table expression> clause to filter out rows from the source or target tables. Diese Methode ähnelt dem Angeben zusätzlicher Suchkriterien in der ON-Klausel und kann zu falschen Ergebnissen führen.This method is similar to specifying additional search criteria in the ON clause and may produce incorrect results. Es wird empfohlen, die Verwendung dieser Methode zu vermeiden oder vor der Implementierung gründlich zu testen.We recommend that you avoid using this method or test thoroughly before implementing it.

Der Joinvorgang in der MERGE-Anweisung wird auf dieselbe Weise optimiert wie ein Join in einer SELECT-Anweisung.The join operation in the MERGE statement is optimized in the same way as a join in a SELECT statement. Das heißt, beim Verarbeiten von Joins durch SQL Server wählt der Abfrageoptimierer (aus verschiedenen Möglichkeiten) die effizienteste Methode aus.That is, when SQL Server processes joins, the query optimizer chooses the most efficient method (out of several possibilities) of processing the join. 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.When the source and target are of similar size and the index guidelines described previously are applied to the source and target tables, a merge join operator is the most efficient query plan. Das liegt daran, dass beide Tabellen einmalig durchsucht werden und die Daten nicht sortiert werden müssen.This is because both tables are scanned once and there is no need to sort the data. Wenn die Quelltabelle kleiner als die Zieltabelle ist, ist ein Nested Loops-Operator vorzuziehen.When the source is smaller than the target table, a nested loops operator is preferable.

Sie können die Verwendung eines bestimmten Joins erzwingen, indem Sie in der MERGE-Anweisung die OPTION (<query_hint>)-Klausel angeben.You can force the use of a specific join by specifying the OPTION (<query_hint>) clause in the MERGE statement. Es wird empfohlen, als Abfragehinweis für MERGE-Anweisungen nicht den Hashjoin zu verwenden, weil dieser Jointyp keine Indizes verwendet.We recommend that you do not use the hash join as a query hint for MERGE statements because this join type does not use indexes.

Bewährte Methoden für die ParametrisierungParameterization Best Practices

Wenn eine der SELECT-, INSERT-, UPDATE- oder DELETE-Anweisungen ohne Parameter ausgeführt wird, kann der SQL Server-Abfrageoptimierer die Anweisung intern parametrisieren.If a SELECT, INSERT, UPDATE, or DELETE statement is executed without parameters, the SQL Server query optimizer may choose to parameterize the statement internally. Dies bedeutet, dass alle eventuell in der Abfrage enthaltenen Literalwerte durch Parameter ersetzt werden.This means that any literal values that are contained in the query are substituted with parameters. Beispielsweise kann die Anweisung „INSERT dbo.MyTable (Col1, Col2) VALUES (1, 10)“ intern als „INSERT dbo.MyTable (Col1, Col2) VALUES (@p1, @p2)“ implementiert werden.For example, the statement INSERT dbo.MyTable (Col1, Col2) VALUES (1, 10), may be implemented internally as INSERT dbo.MyTable (Col1, Col2) VALUES (@p1, @p2). Dieser als einfache Parametrisierung bezeichnete Vorgang erhöht die Wahrscheinlichkeit, dass die relationale Engine neue SQL-Anweisungen vorhandenen, zuvor kompilierten Ausführungsplänen zuordnet.This process, called simple parameterization, increases the ability of the relational engine to match new SQL statements with existing, previously-compiled execution plans. Möglicherweise wird die Abfrageleistung verbessert, da die Häufigkeit der Abfragekompilierungen und Neukompilierungen verringert wird.Query performance may be improved because the frequency of query compilations and recompilations are reduced. Die einfache Parametrisierung wird vom Abfrageoptimierer nicht auf MERGE-Anweisungen angewendet.The query optimizer does not apply the simple parameterization process to MERGE statements. Deshalb ist die Leistung bei der Ausführung von MERGE-Anweisungen mit Literalwerten nicht so hoch wie bei einzelnen INSERT-, UPDATE- oder DELETE-Anweisungen, weil bei jeder Ausführung der MERGE-Anweisung ein neuer Plan kompiliert wird.Therefore, MERGE statements that contain literal values may not perform as well as individual INSERT, UPDATE, or DELETE statements because a new plan is compiled each time the MERGE statement is executed.

Um die Abfrageleistung zu verbessern, werden die folgenden Parametrisierungsrichtlinien empfohlen:To improve query performance, we recommend the following parameterization guidelines:

  • Parametrisieren Sie alle Literalwerte in der ON <merge_search_condition>-Klausel sowie in den WHEN-Klauseln der MERGE-Anweisung.Parameterize all literal values in the ON <merge_search_condition> clause and in the the WHEN clauses of the MERGE statement. Beispielsweise können Sie die MERGE-Anweisung in eine gespeicherte Prozedur integrieren und dabei die Literalwerte durch die entsprechenden Eingabeparameter ersetzen.For example, you can incorporate the MERGE statement into a stored procedure replacing the literal values with appropriate input parameters.
  • 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.If you cannot parameterize the statement, create a plan guide of type TEMPLATE and specify the PARAMETERIZATION FORCED query hint in the plan guide.
  • 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.If MERGE statements are executed frequently on the database, consider setting the PARAMETERIZATION option on the database to FORCED. Legen Sie diese Option mit Bedacht fest.Use caution when setting this option. Die PARAMETERIZATION-Option ist eine Einstellung auf Datenbankebene und wirkt sich auf die Verarbeitung aller Abfragen für die Datenbank aus.The PARAMETERIZATION option is a database-level setting and affects how all queries against the database are processed.

Bewährte Methoden für die TOP-KlauselTOP Clause Best Practices

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 entfernt wurden, auf die keine INSERT-, UPDATE- oder DELETE-Aktion angewendet wird.In the MERGE statement, the TOP clause specifies the number or percentage of rows that are affected after the source table and the target table are joined, and after rows that do not qualify for an insert, update, or delete action are removed. 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.The TOP clause further reduces the number of joined rows to the specified value and the insert, update, or delete actions are applied to the remaining joined rows in an unordered fashion. Dies bedeutet, dass für die Verteilung der Zeilen auf die in den WHEN-Klauseln definierten Aktionen keine bestimmte Reihenfolge gilt.That is, there is no order in which the rows are distributed among the actions defined in the WHEN clauses. Wenn beispielsweise „TOP (10)“ angegeben wird, sind 10 Zeilen betroffen. Von diesen Zeilen können 7 aktualisiert und 3 eingefügt werden, oder 1 Zeile kann gelöscht, 5 können aktualisiert und 4 eingefügt werden usw.For example, specifying TOP (10) affects 10 rows; of these rows, 7 may be updated and 3 inserted, or 1 may be deleted, 5 updated, and 4 inserted and so on.

Häufig wird die TOP-Klausel zur Batchausführung von DML-Vorgängen (Data Manipulation Language) für eine umfangreiche Tabelle verwendet.It is common to use the TOP clause to perform data manipulation language (DML) operations on a large table in batches. Wenn Sie die TOP-Klausel zu diesem Zweck in der MERGE-Anweisung verwenden, müssen Sie sich der folgenden Auswirkungen bewusst sein.When using the TOP clause in the MERGE statement for this purpose, it is important to understand the following implications.

  • Die E/A-Leistung ist möglicherweise betroffen.I/O performance may be affected.

    Die MERGE-Anweisung führt einen vollständigen Tabellenscan der Quell- und Zieltabellen aus.The MERGE statement performs a full table scan of both the source and target tables. Durch die Aufteilung des Vorgangs in Batches wird die Anzahl von Schreibvorgängen pro Batch reduziert. Für jeden Batch wird jedoch ein vollständiger Tabellenscan der Quell- und der Zieltabellen ausgeführt.Dividing the operation into batches reduces the number of write operations performed per batch; however, each batch will perform a full table scan of the source and target tables. Die resultierende Leseaktivität wirkt sich möglicherweise auf die Leistung der Abfrage aus.The resulting read activity may affect the performance of the query.

  • Es können falsche Ergebnisse auftreten.Incorrect results can occur.

    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.It is important to ensure that all successive batches target new rows or undesired behavior such as incorrectly inserting duplicate rows into the target table can occur. Dies kann passieren, wenn die Quelltabelle eine Zeile enthält, die nicht im Zielbatch, aber in der Zieltabelle insgesamt enthalten war.This can happen when the source table includes a row that was not in a target batch but was in the overall target table.

  • So stellen Sie die Richtigkeit der Ergebnisse sicherTo insure correct results:

    • Bestimmen Sie mithilfe der ON-Klausel die Quellzeilen, die sich auf vorhandene Zielzeilen auswirken bzw. tatsächlich neu sind.Use the ON clause to determine which source rows affect existing target rows and which are genuinely new.
    • Bestimmen Sie mithilfe einer zusätzlichen Bedingung in der WHEN MATCHED-Klausel, ob die Zielzeile bereits in einem früheren Batch aktualisiert wurde.Use an additional condition in the WHEN MATCHED clause to determine if the target row has already been updated by a previous batch.

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.Because the TOP clause is only applied after these clauses are applied, each execution either inserts one genuinely unmatched row or updates one existing row.

Bewährte Methoden zum MassenladenBulk Load Best Practices

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.The MERGE statement can be used to efficiently bulk load data from a source data file into a target table by specifying the OPENROWSET(BULK…) clause as the table source. Dadurch wird die gesamte Datei als einzelner Batch verarbeitet.By doing so, the entire file is processed in a single batch.

Zur Leistungsverbesserung des Massenladevorgangs werden die folgenden Richtlinien empfohlen:To improve the performance of the bulk merge process, we recommend the following guidelines:

  • Erstellen Sie für die Joinspalten in der Zieltabelle einen gruppierten Index.Create a clustered index on the join columns in the target table.

  • Geben Sie mithilfe des ORDER-Hinweises und des UNIQUE-Hinweises in der OPENROWSET(BULK…)-Klausel an, wie die Quelldatendatei sortiert ist.Use the ORDER and UNIQUE hints in the OPENROWSET(BULK…) clause to specify how the source data file is sorted.

    Standardmäßig geht der Massenvorgang davon aus, dass die Datendatei nicht sortiert ist.By default, the bulk operation assumes the data file is unordered. 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.Therefore, it is important that the source data is sorted according to the clustered index on the target table and that the ORDER hint is used to indicate the order so that the query optimizer can generate a more efficient query plan. Hinweise werden zur Laufzeit validiert. Wenn der Datenstrom nicht mit den angegebenen Hinweisen übereinstimmt, wird ein Fehler ausgelöst.Hints are validated at runtime; if the data stream does not conform to the specified hints, an error is raised.

Mit diesen Richtlinien wird sichergestellt, dass die Joinschlüssel eindeutig sind und die Sortierreihenfolge der Daten in der Quelldatei mit der Zieltabelle übereinstimmt.These guidelines ensure that the join keys are unique and the sort order of the data in the source file matches the target table. Die Abfrageleistung wird verbessert, da keine zusätzlichen Sortiervorgänge erforderlich sind und keine unnötigen Datenkopien angefordert werden.Query performance is improved because additional sort operations are not necessary and unnecessary data copies are not required.

Messen und Diagnostizieren der MERGE-LeistungMeasuring and Diagnosing MERGE Performance

Die folgenden Funktionen stehen Ihnen zur Verfügung, um die Leistung von MERGE-Anweisungen zu messen und zu diagnostizieren.The following features are available to assist you in measuring and diagnosing the performance of MERGE statements.

  • Geben Sie mithilfe des merge stmt-Indikators in der dynamischen Verwaltungssicht sys.dm_exec_query_optimizer_info die Anzahl von Abfrageoptimierungen für MERGE-Anweisungen zurück.Use the merge stmt counter in the sys.dm_exec_query_optimizer_info dynamic management view to return the number of query optimizations that are for MERGE statements.
  • 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.Use the merge_action_type attribute in the sys.dm_exec_plan_attributes dynamic management view to return the type of trigger execution plan used as the result of a MERGE statement.
  • Erfassen Sie mit der SQL-Ablaufverfolgung auf dieselbe Weise Problembehandlungsdaten für die MERGE-Anweisung wie für andere DML-Anweisungen (Data Manipulation Language).Use SQL Trace to gather troubleshooting data for the MERGE statement in the same way you would for other data manipulation language (DML) statements. Weitere Informationen finden Sie unter SQL Trace.For more information, see SQL Trace.

BeispieleExamples

A.A. Verwenden von MERGE zum Ausführen von INSERT- und UPDATE-Vorgängen für eine Tabelle in einer einzelnen AnweisungUsing MERGE to do INSERT and UPDATE operations on a table in a single statement

Ein häufiges Szenario ist die Aktualisierung einer oder mehrerer Spalten in einer Tabelle, wenn eine übereinstimmende Zeile vorhanden ist.A common scenario is updating one or more columns in a table if a matching row exists. Anderenfalls, wenn keine übereinstimmende Zeile vorhanden ist, das Einfügen der Daten als neue Zeile.Or, inserting the data as a new row if a matching row doesn't exist. In jedem Szenario übergeben Sie normalerweise Parameter an eine gespeicherte Prozedur, die die entsprechende UPDATE-Anweisung und INSERT-Anweisung enthält.You usually do either scenario by passing parameters to a stored procedure that contains the appropriate UPDATE and INSERT statements. Mit der MERGE-Anweisung können Sie beide Tasks in einer einzelnen Anweisung ausführen.With the MERGE statement, you can do both tasks in a single statement. Im folgenden Beispiel wird eine gespeicherte Prozedur in der AdventureWorks2012AdventureWorks2012-Datenbank dargestellt, die sowohl eine INSERT-Anweisung als auch eine UPDATE-Anweisung enthält.The following example shows a stored procedure in the AdventureWorks2012AdventureWorks2012database that contains both an INSERT statement and an UPDATE statement. Anschließend wird die Prozedur so geändert, dass sie die entsprechenden Vorgänge mit einer einzelnen MERGE-Anweisung ausführt.The procedure is then modified to run the equivalent operations by using a single MERGE statement.

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 target  
    USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name)  
    ON (target.UnitMeasureCode = source.UnitMeasureCode)  
    WHEN MATCHED THEN
        UPDATE SET Name = source.Name  
    WHEN NOT MATCHED THEN  
        INSERT (UnitMeasureCode, Name)  
        VALUES (source.UnitMeasureCode, source.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  

B.B. Verwenden von MERGE zum Ausführen von UPDATE- und DELETE-Operationen für eine Tabelle in einer einzelnen AnweisungUsing MERGE to do UPDATE and DELETE operations on a table in a single statement

Im folgenden Beispiel wird die Tabelle ProductInventory in der AdventureWorks2012AdventureWorks2012-Beispieldatenbank täglich mit MERGE aktualisiert. Dies erfolgt auf der Grundlage der in der Tabelle SalesOrderDetail verarbeiteten Bestellungen.The following example uses MERGE to update the ProductInventory table in the AdventureWorks2012AdventureWorks2012 sample database, daily, based on orders that are processed in the SalesOrderDetail table. 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.The Quantity column of the ProductInventory table is updated by subtracting the number of orders placed each day for each product in the SalesOrderDetail table. 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.If the number of orders for a product drops the inventory level of a product to 0 or less, the row for that product is deleted from the ProductInventory table.

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

C.C. Verwenden von MERGE zum Ausführen von UPDATE- und INSERT-Vorgängen für eine Zieltabelle unter Verwendung einer abgeleiteten QuelltabelleUsing MERGE to do UPDATE and INSERT operations on a target table by using a derived source table

Im folgenden Beispiel wird die SalesReason-Tabelle in der AdventureWorks2012AdventureWorks2012-Datenbank durch das Aktualisieren oder Einfügen von Zeilen mithilfe von MERGE geändert.The following example uses MERGE to modify the SalesReason table in the AdventureWorks2012AdventureWorks2012 database by either updating or inserting rows. Wenn der Wert von NewName in der Quelltabelle einem Wert in der Name-Spalte der Zieltabelle entspricht (SalesReason), wird die ReasonType-Spalte in der Zieltabelle aktualisiert.When the value of NewName in the source table matches a value in the Name column of the target table, (SalesReason), the ReasonType column is updated in the target table. Wenn der Wert von NewName jedoch nicht übereinstimmt, wird die Quellzeile in die Zieltabelle eingefügt.When the value of NewName doesn't match, the source row is inserted into the target table. Die Quelltabelle ist eine abgeleitete Tabelle, die mithilfe des Transact-SQLTransact-SQL-Tabellenwertkonstruktors mehrere Zeilen für die Quelltabelle angibt.The source table is a derived table that uses the Transact-SQLTransact-SQL table value constructor to specify multiple rows for the source table. Weitere Informationen zum Verwenden des Tabellenwertkonstruktors in einer abgeleiteten Tabelle finden Sie unter Table Value Constructor (Transact-SQL) (Tabellenwertkonstruktor (Transact-SQL)).For more information about using the table value constructor in a derived table, see Table Value Constructor (Transact-SQL). Außerdem zeigt das Beispiel, wie die Ergebnisse der OUTPUT-Klausel in einer Tabellenvariablen gespeichert werden.The example also shows how to store the results of the OUTPUT clause in a table variable. Und dann fassen Sie die Ergebnisse der MERGE-Anweisung zusammen, indem Sie einen einfachen SELECT-Vorgang ausführen, der die Anzahl der eingefügten und aktualisierten Zeilen zurückgibt.And, then you summarize the results of the MERGE statement by running a simple select operation that returns the count of inserted and updated rows.

-- Create a temporary table variable to hold the output actions.  
DECLARE @SummaryOfChanges TABLE(Change VARCHAR(20));  
  
MERGE INTO Sales.SalesReason AS Target  
USING (VALUES ('Recommendation','Other'), ('Review', 'Marketing'),
              ('Internet', 'Promotion'))  
       AS Source (NewName, NewReasonType)  
ON Target.Name = Source.NewName  
WHEN MATCHED THEN  
UPDATE SET ReasonType = Source.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;  

D.D. Einfügen der Ergebnisse der MERGE-Anweisung in eine andere TabelleInserting the results of the MERGE statement into another table

Im folgenden Beispiel werden aus der OUTPUT-Klausel einer MERGE-Anweisung zurückgegebene Daten erfasst und in eine andere Tabelle eingefügt.The following example captures data returned from the OUTPUT clause of a MERGE statement and inserts that data into another table. Die MERGE-Anweisung aktualisiert die Quantity-Spalte der ProductInventory-Tabelle in der AdventureWorks2012AdventureWorks2012-Datenbank täglich auf der Grundlage der Bestellungen, die in der SalesOrderDetail-Tabelle verarbeitet werden.The MERGE statement updates the Quantity column of the ProductInventory table in the AdventureWorks2012AdventureWorks2012 database, based on orders that are processed in the SalesOrderDetail table. In diesem Beispiel werden die aktualisierten Zeilen erfasst und in eine andere Tabelle eingefügt, in der Bestandsänderungen nachverfolgt werden.The example captures the updated rows and inserts them into another table that's used to track inventory changes.

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  
            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  

E.E. Verwenden von MERGE zum Ausführen von INSERT oder UPDATE auf eine Edge-Zieltabelle in einer GraphdatenbankUsing MERGE to do INSERT or UPDATE on a target edge table in a graph database

In diesem Beispiel erstellen Sie Knotentabellen Person und City und eine Edgetabelle livesIn.In this example, you create node tables Person and City and an edge table livesIn. Sie verwenden die MERGE-Anweisung auf dem livesIn-Edge und fügen eine neue Zeile ein, wenn der Edge zwischen Person und City noch nicht vorhanden ist.You use the MERGE statement on the livesIn edge and insert a new row if the edge doesn't already exist between a Person and City. Wenn der Edge bereits vorhanden ist, aktualisieren Sie nur das StreetAddress-Attribut auf dem livesIn-Edge.If the edge already exists, then you just update the StreetAddress attribute on the livesIn edge.

-- 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

Weitere InformationenSee Also