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

S’APPLIQUE À : OuiSQL Server OuiAzure SQL Database NonAzure Synapse Analytics (SQL DW) NonParallel Data Warehouse APPLIES TO: YesSQL Server YesAzure SQL Database NoAzure Synapse Analytics (SQL DW) NoParallel Data Warehouse

Exécute des opérations d'insertion, de mise à jour ou de suppression sur une table cible à partir des résultats d'une jointure avec une table source.Runs insert, update, or delete operations on a target table from the results of a join with a source table. Par exemple, synchronisez deux tables en insérant, mettant à jour ou supprimant des lignes dans une seule table selon les différences trouvées dans l'autre table.For example, synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.

Conseil relatif aux performances : Le comportement conditionnel décrit pour l'instruction MERGE fonctionne mieux lorsque les deux tables ont un mélange complexe de caractéristiques correspondantes.Performance Tip: The conditional behavior described for the MERGE statement works best when the two tables have a complex mixture of matching characteristics. Par exemple, l'insertion d'une ligne si elle n'existe pas ou la mise à jour d’une ligne si elle correspond.For example, inserting a row if it doesn't exist, or updating a row if it matches. Vous pouvez améliorer les performances et l'extensibilité lors d'une simple mise à jour d'une table basée sur les lignes d'une autre table en utilisant les instructions INSERT, UPDATE et DELETE.When simply updating one table based on the rows of another table, improve the performance and scalability with basic INSERT, UPDATE, and DELETE statements. Par exemple :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);  

Icône du lien de rubrique Conventions de la syntaxe Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SyntaxeSyntax

[ 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  

ArgumentsArguments

WITH <common_table_expression>WITH <common_table_expression>
Spécifie le jeu de résultats ou la vue nommés temporaires, également appelés expression de table commune et définis dans le cadre de l'instruction MERGE.Specifies the temporary named result set or view, also known as common table expression, that's defined within the scope of the MERGE statement. Le jeu de résultats est dérivé d'une simple requête et est référencé par l'instruction MERGE.The result set derives from a simple query and is referenced by the MERGE statement. Pour plus d’informations, consultez WITH common_table_expression (Transact-SQL).For more information, see WITH common_table_expression (Transact-SQL).

TOP ( expression ) [ PERCENT ]TOP ( expression ) [ PERCENT ]
Spécifie le nombre ou le pourcentage de lignes affectées.Specifies the number or percentage of affected rows. L’argument expression peut être un nombre ou un pourcentage de lignes.expression can be either a number or a percentage of the rows. Les lignes référencées dans l'expression TOP ne sont pas triées dans un ordre donné.The rows referenced in the TOP expression are not arranged in any order. Pour plus d’informations, consultez TOP (Transact-SQL).For more information, see TOP (Transact-SQL).

La clause TOP est appliquée après la jointure de l'intégralité de la table source et de la table cible, et après la suppression des lignes jointes qui ne sont pas éligibles pour une opération de type INSERT, UPDATE ou DELETE.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. La clause TOP réduit le nombre de lignes jointes à la valeur spécifiée.The TOP clause further reduces the number of joined rows to the specified value. Les actions d’insertion, de mise à jour ou de suppression s’appliquent aux lignes jointes restantes de manière non ordonnée.The insert, update, or delete actions apply to the remaining joined rows in an unordered way. Les lignes ne sont donc pas réparties selon un ordre particulier dans le cadre des actions définies dans les clauses WHEN.That is, there's no order in which the rows are distributed among the actions defined in the WHEN clauses. Par exemple, la spécification de la clause TOP (10) affecte 10 lignes.For example, specifying TOP (10) affects 10 rows. Parmi ces lignes, 7 peuvent être mises à jour et 3 insérées, ou alors 1 ligne peut être supprimée, 5 mises à jour et 4 insérées, et ainsi de suite.Of these rows, 7 may be updated and 3 inserted, or 1 may be deleted, 5 updated, and 4 inserted, and so on.

Étant donné que l'instruction MERGE effectue une analyse complète des tables source et cible, les performances d'E/S peuvent être affectées lorsque la clause TOP est utilisée pour modifier une table volumineuse en créant plusieurs lots.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. Dans ce scénario, il est important de s’assurer que tous les lots consécutifs ciblent les nouvelles lignes.In this scenario, it's important to ensure that all successive batches target new rows.

database_namedatabase_name
Nom de la base de données contenant target_table.The name of the database in which target_table is located.

schema_nameschema_name
Nom du schéma auquel target_table appartient.The name of the schema to which target_table belongs.

target_tabletarget_table
Table ou vue à laquelle les lignes de données de <table_source> sont comparées sur la base de <clause_search_condition>.The table or view against which the data rows from <table_source> are matched based on <clause_search_condition>. target_table est la cible de toute opération de type INSERT, UPDATE ou DELETE spécifiée par les clauses WHEN de l’instruction MERGE.target_table is the target of any insert, update, or delete operations specified by the WHEN clauses of the MERGE statement.

Si target_table est une vue, toutes les opérations dont elle fait l’objet doivent satisfaire aux conditions requises pour la mise à jour des vues.If target_table is a view, any actions against it must satisfy the conditions for updating views. Pour plus d’informations, consultez Modifier les données par l’intermédiaire d’une vue.For more information, see Modify Data Through a View.

target_table ne peut pas être une table distante.target_table can't be a remote table. Aucune règle ne peut être définie sur target_table.target_table can't have any rules defined on it.

[ AS ] table_alias[ AS ] table_alias
Autre nom utilisé pour faire référence à une table.An alternative name to reference a table.

USING <table_source>USING <table_source>
Spécifie la source de données correspondant aux lignes de données dans target_table en fonction de <merge_search condition>.Specifies the data source that's matched with the data rows in target_table based on <merge_search condition>. Le résultat de cette correspondance dicte les actions à effectuer par les clauses WHEN de l'instruction MERGE.The result of this match dictates the actions to take by the WHEN clauses of the MERGE statement. <table_source> peut être une table distante ou une table dérivée qui accède à des tables distantes.<table_source> can be a remote table or a derived table that accesses remote tables.

<table_source> peut être une table dérivée qui utilise le constructeur de valeurs de table Transact-SQLTransact-SQL pour construire une table en spécifiant plusieurs lignes.<table_source> can be a derived table that uses the Transact-SQLTransact-SQL table value constructor to construct a table by specifying multiple rows.

Pour plus d’informations sur la syntaxe et les arguments de cette clause, consultez 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>
Spécifie les conditions de jointure de <table_source> avec target_table afin de déterminer où la correspondance a lieu.Specifies the conditions on which <table_source> joins with target_table to determine where they match.

Attention

Il est important de spécifier uniquement les colonnes de la table cible utilisées à des fins de correspondance.It's important to specify only the columns from the target table to use for matching purposes. Autrement dit, spécifiez les colonnes de la table cible qui seront comparées à la colonne correspondante de la table source.That is, specify columns from the target table that are compared to the corresponding column of the source table. N'essayez pas d'améliorer les performances des requêtes en éliminant par filtrage des lignes de la table cible dans la clause ON, via la spécification de AND NOT target_table.column_x = value, par exemple.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. Cette approche peut retourner des résultats inattendus et incorrects.Doing so may return unexpected and incorrect results.

WHEN MATCHED THEN <merge_matched>WHEN MATCHED THEN <merge_matched>
Spécifie que toutes les lignes de *target_table qui correspondent aux lignes retournées par <table_source> ON <merge_search_condition>, et qui répondent aux critères de recherche supplémentaires, sont mises à jour ou supprimées en fonction de la clause <merge_matched>.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.

L'instruction MERGE peut avoir au plus deux clauses WHEN MATCHED.The MERGE statement can have, at most, two WHEN MATCHED clauses. Si deux clauses sont spécifiées, la première clause doit être accompagnée d’une clause AND <search_condition>.If two clauses are specified, the first clause must be accompanied by an AND <search_condition> clause. Pour toute ligne donnée, la deuxième clause WHEN MATCHED est appliquée uniquement si la première ne l'est pas.For any given row, the second WHEN MATCHED clause is only applied if the first isn't. En présence de deux clauses WHEN MATCHED, l'une d'elles doit spécifier une action UPDATE et l'autre une action DELETE.If there are two WHEN MATCHED clauses, one must specify an UPDATE action and one must specify a DELETE action. Lorsque l’action UPDATE est spécifiée dans la clause <merge_matched> et que plusieurs lignes de <table_source> correspondent à une ligne dans target_table en fonction de <merge_search_condition>, SQL ServerSQL Server retourne une erreur.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. L'instruction MERGE ne peut pas mettre à jour la même ligne plus d'une fois, ou mettre à jour et supprimer la même ligne.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>
Spécifie qu’une ligne est insérée dans target_table pour chaque ligne retournée par <table_source> ON <merge_search_condition> qui ne correspond pas à une ligne dans target_table, mais satisfait à un critère de recherche supplémentaire, le cas échéant.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. Les valeurs à insérer sont spécifiées par la clause <merge_not_matched>.The values to insert are specified by the <merge_not_matched> clause. L’instruction MERGE peut avoir une seule clause WHEN NOT MATCHED [ BY TARGET ].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>
Spécifie que toutes les lignes de *target_table qui ne correspondent pas aux lignes retournées par <table_source> ON <merge_search_condition>, et qui répondent aux critères de recherche supplémentaires, sont mises à jour ou supprimées selon la clause <merge_matched>.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.

L'instruction MERGE peut avoir au plus deux clauses WHEN NOT MATCHED BY SOURCE.The MERGE statement can have at most two WHEN NOT MATCHED BY SOURCE clauses. Si deux clauses sont spécifiées, la première clause doit être accompagnée d’une clause AND <clause_search_condition>.If two clauses are specified, then the first clause must be accompanied by an AND <clause_search_condition> clause. Pour toute ligne donnée, la deuxième clause WHEN NOT MATCHED BY SOURCE est appliquée uniquement si la première ne l'est pas.For any given row, the second WHEN NOT MATCHED BY SOURCE clause is only applied if the first isn't. En présence de deux clauses WHEN NOT MATCHED BY SOURCE, l'une d'elles doit spécifier une action UPDATE et l'autre une action DELETE.If there are two WHEN NOT MATCHED BY SOURCE clauses, then one must specify an UPDATE action and one must specify a DELETE action. Seules les colonnes de la table cible peuvent être référencées dans <clause_search_condition>.Only columns from the target table can be referenced in <clause_search_condition>.

Quand aucune ligne n’est retournée par <table_source>, les colonnes de la table source ne sont pas accessibles.When no rows are returned by <table_source>, columns in the source table can't be accessed. Si l’opération de mise à jour ou de suppression spécifiée dans la clause <merge_matched> référence des colonnes dans la table source, l’erreur 207 (nom de colonne non valide) est retournée.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. Par exemple, la clause WHEN NOT MATCHED BY SOURCE THEN UPDATE SET TargetTable.Col1 = SourceTable.Col1 peut faire en sorte que l'instruction échoue dans la mesure où Col1 dans la table source est inaccessible.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>
Spécifie toute condition de recherche valide.Specifies any valid search condition. Pour plus d’informations, consultez Condition de recherche (Transact-SQL).For more information, see Search Condition (Transact-SQL).

<table_hint_limited><table_hint_limited>
Spécifie un ou plusieurs indicateurs de table qui sont appliqués à la table cible pour chaque opération INSERT, UPDATE ou DELETE exécutée par l'instruction MERGE.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. Le mot clé WITH et les parenthèses sont obligatoires.The WITH keyword and the parentheses are required.

NOLOCK et READUNCOMMITTED ne sont pas autorisés.NOLOCK and READUNCOMMITTED aren't allowed. Pour plus d’informations sur les indicateurs de table, consultez Indicateurs de table (Transact-SQL).For more information about table hints, see Table Hints (Transact-SQL).

La spécification de l'indicateur TABLOCK sur une table qui est la cible d'une instruction INSERT a le même effet que la spécification de l'indicateur TABLOCKX.Specifying the TABLOCK hint on a table that's the target of an INSERT statement has the same effect as specifying the TABLOCKX hint. Un verrou exclusif est appliqué à la table.An exclusive lock is taken on the table. Lorsque FORCESEEK est spécifié, il s'applique à l'instance implicite de la table cible jointe à la table source.When FORCESEEK is specified, it applies to the implicit instance of the target table joined with the source table.

Attention

Le fait de spécifier READPAST avec WHEN NOT MATCHED [ BY TARGET ] THEN INSERT peut se traduire par des opérations INSERT qui violent des contraintes UNIQUE.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 ] )
Spécifie le nom ou l'ID d'un ou de plusieurs index sur la table cible pour effectuer une jointure implicite avec la table source.Specifies the name or ID of one or more indexes on the target table for doing an implicit join with the source table. Pour plus d’informations, consultez Indicateurs de table (Transact-SQL).For more information, see Table Hints (Transact-SQL).

<OUTPUT_Clause><output_clause>
Retourne une ligne pour chaque ligne dans target_table qui est mise à jour, insérée ou supprimée, peu importe l’ordre.Returns a row for every row in target_table that's updated, inserted, or deleted, in no particular order. $action peut être spécifié dans la clause de sortie.$action can be specified in the output clause. $action est une colonne de type nvarchar(10) qui retourne l’une des trois valeurs possibles pour chaque ligne : « INSERT », « UPDATE » ou « DELETE », en fonction de l'action effectuée sur cette ligne.$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. Pour plus d’informations sur les arguments et le comportement de cette clause, consultez Clause OUTPUT (Transact-SQL).For more information about the arguments and behavior of this clause, see OUTPUT Clause (Transact-SQL).

OPTION ( <query_hint> [ ,...n ] )OPTION ( <query_hint> [ ,...n ] )
Spécifie que des indicateurs de l'optimiseur sont utilisés pour personnaliser la façon dont le moteur de base de données traite l'instruction.Specifies that optimizer hints are used to customize the way the Database Engine processes the statement. Pour plus d’informations, consultez Indicateurs de requête (Transact-SQL).For more information, see Query Hints (Transact-SQL).

<merge_matched><merge_matched>
Spécifie l’opération de mise à jour ou suppression qui s’applique à toutes les lignes de target_table qui ne correspondent pas aux lignes retournées par <table_source> ON <merge_search_condition>, et qui satisfont à toute condition de recherche supplémentaire.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>
Spécifie la liste de noms de colonne ou de variable à mettre à jour dans la table cible et les valeurs avec lesquelles les mettre à jour.Specifies the list of column or variable names to update in the target table and the values with which to update them.

Pour plus d’informations sur les arguments de cette clause, consultez UPDATE (Transact-SQL).For more information about the arguments of this clause, see UPDATE (Transact-SQL). La définition d'une variable à la même valeur qu'une colonne n'est pas prise en charge.Setting a variable to the same value as a column isn't supported.

SuppressionDELETE
Spécifie que les lignes qui correspondent aux lignes dans target_table sont supprimées.Specifies that the rows matching rows in target_table are deleted.

<merge_not_matched><merge_not_matched>
Spécifie les valeurs à insérer dans la table cible.Specifies the values to insert into the target table.

(column_list)(column_list)
Liste d'une ou de plusieurs colonnes de la table cible dans lesquelles insérer des données.A list of one or more columns of the target table in which to insert data. Les colonnes doivent être spécifiées comme un nom en une seule partie sinon l'instruction MERGE échouera.Columns must be specified as a single-part name or else the MERGE statement will fail. column_list doit être placé entre parenthèses et délimité par des virgules.column_list must be enclosed in parentheses and delimited by commas.

VALUES ( values_list)VALUES ( values_list)
Liste séparée par des virgules et contenant des constantes, variables ou expressions qui retourne les valeurs à insérer dans la table cible.A comma-separated list of constants, variables, or expressions that return values to insert into the target table. Les expressions ne peuvent pas contenir d'instruction EXECUTE.Expressions can't contain an EXECUTE statement.

DEFAULT VALUESDEFAULT VALUES
Force la ligne insérée à prendre les valeurs par défaut définies pour chaque colonne.Forces the inserted row to contain the default values defined for each column.

Pour plus d’informations sur cette clause, consultez INSERT (Transact-SQL).For more information about this clause, see INSERT (Transact-SQL).

<search condition><search condition>
Spécifie les conditions de recherche utilisées pour spécifier <merge_search_condition> ou <clause_search_condition>.Specifies the search conditions to specify <merge_search_condition> or <clause_search_condition>. Pour plus d’informations sur les arguments pour cette clause, consultez Condition de recherche (Transact-SQL).For more information about the arguments for this clause, see Search Condition (Transact-SQL).

<graph search pattern><graph search pattern>
Spécifie le modèle de correspondance de graphe.Specifies the graph match pattern. Pour plus d’informations sur les arguments de cette clause, consultez MATCH (Transact-SQL).For more information about the arguments for this clause, see MATCH (Transact-SQL)

Notes Remarks

Au moins l'une des trois clauses MATCHED doit être spécifiée, mais cela peut être dans n'importe quel ordre.At least one of the three MATCHED clauses must be specified, but they can be specified in any order. Une variable ne peut pas être mise à jour plus d'une fois dans la même clause MATCHED.A variable can't be updated more than once in the same MATCHED clause.

Toute opération d'insertion, de mise à jour ou de suppression spécifiée sur la table cible par l'instruction MERGE est limitée par toutes contraintes qui s'appliquent à elle, notamment les contraintes d'intégrité référentielle en cascade.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. Si IGNORE_DUP_KEY a la valeur ON pour un index unique sur la table cible, MERGE ignore ce paramètre.If IGNORE_DUP_KEY is ON for any unique indexes on the target table, MERGE ignores this setting.

L'instruction MERGE requiert un point-virgule (;) comme terminateur d'instruction.The MERGE statement requires a semicolon (;) as a statement terminator. L'erreur 10713 est générée lorsqu'une instruction MERGE est exécutée sans le terminateur.Error 10713 is raised when a MERGE statement is run without the terminator.

En cas d’utilisation après MERGE, @@ROWCOUNT (Transact-SQL) retourne au client le nombre total de lignes insérées, mises à jour et supprimées.When used after MERGE, @@ROWCOUNT (Transact-SQL) returns the total number of rows inserted, updated, and deleted to the client.

MERGE est un mot clé entièrement réservé lorsque le niveau de compatibilité de la base de données a la valeur 100 ou une valeur supérieure.MERGE is a fully reserved keyword when the database compatibility level is set to 100 or higher. L'instruction MERGE est disponible sous les niveaux de compatibilité de base de données 90 et 100 ; cependant, le mot clé n'est pas entièrement réservé lorsque le niveau de compatibilité de la base de données a la valeur 90.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.

N’utilisez pas l’instruction MERGE avec la réplication de mise à jour en attente.Don't use the MERGE statement when using queued updating replication. MERGE et le déclencheur de mise à jour en attente ne sont pas compatibles.The MERGE and queued updating trigger aren't compatible. Remplacez l’instruction MERGE par une instruction d’insertion (INSERT) ou de mise à jour (UPDATE).Replace the MERGE statement with an insert or an update statement.

Implémentation de déclencheurTrigger Implementation

Pour chaque opération INSERT, UPDATE ou DELETE spécifiée dans l'instruction MERGE, SQL ServerSQL Server lance tous les déclencheurs AFTER correspondants définis sur la table cible, mais ne garantit pas l'opération sur laquelle les déclencheurs seront lancés en premier ou en dernier.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. Les déclencheurs définis pour la même opération respectent l'ordre que vous spécifiez.Triggers defined for the same action honor the order you specify. Pour plus d’informations sur le paramétrage de l’ordre de lancement des déclencheurs, consultez Spécifier les premier et dernier déclencheurs.For more information about setting trigger firing order, see Specify First and Last Triggers.

Si la table cible a un déclencheur INSTEAD OF actif défini pour une opération INSERT, UPDATE ou DELETE effectuée par une instruction MERGE, elle doit avoir un déclencheur INSTEAD OF actif pour toutes les opérations spécifiées dans l'instruction MERGE.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.

Si des déclencheurs INSTEAD OF UPDATE ou INSTEAD OF DELETE sont définis sur target_table, les opérations UPDATE ou DELETE ne sont pas exécutées.If any INSTEAD OF UPDATE or INSTEAD OF DELETE triggers are defined on target_table, the update or delete operations aren't run. À la place, les déclencheurs sont lancés et les tables insérées et supprimées sont remplies en conséquence.Instead, the triggers fire and the inserted and deleted tables then populate accordingly.

Si des déclencheurs INSTEAD OF INSERT sont définis sur target_table, l’opération INSERT n’est pas effectuée.If any INSTEAD OF INSERT triggers are defined on target_table, the insert operation isn't performed. Au lieu de cela, la table est remplie en conséquence.Instead, the table populates accordingly.

AutorisationsPermissions

Nécessite l'autorisation SELECT sur la table source et les autorisations INSERT, UPDATE ou DELETE sur la table cible.Requires SELECT permission on the source table and INSERT, UPDATE, or DELETE permissions on the target table. Pour plus d’informations, consultez la section Autorisations dans les articles SELECT, INSERT, UPDATE et DELETE.For more information, see the Permissions section in the SELECT, INSERT, UPDATE, and DELETE articles.

Optimisation des performances de l'instruction MERGEOptimizing MERGE Statement Performance

En utilisant l'instruction MERGE, vous pouvez remplacer les instructions individuelles DML par une instruction unique.By using the MERGE statement, you can replace the individual DML statements with a single statement. Vous pouvez ainsi améliorer les performances des requêtes car les opérations sont effectuées dans une seule instruction, cela permettant de réduire le nombre de traitements des données des tables sources et cibles.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. Toutefois, vous ne pouvez obtenir des gains de performance que si vous disposez d'index et de jointures corrects, d'autres considérations entrant également en jeu.However, performance gains depend on having correct indexes, joins, and other considerations in place.

Meilleures pratiques pour les indexIndex Best Practices

Pour améliorer les performances de l'instruction MERGE, nous vous recommandons de respecter les règles suivantes relatives aux index :To improve the performance of the MERGE statement, we recommend the following index guidelines:

  • Créez un index sur les colonnes de jointure de la table source qui sont uniques et qui sont des colonnes de couverture.Create an index on the join columns in the source table that is unique and covering.
  • Créez un index cluster unique sur les colonnes de jointure dans la table cible.Create a unique clustered index on the join columns in the target table.

Ces index garantissent que les clés de jointure sont uniques et que les données des tables sont triées.These indexes ensure that the join keys are unique and the data in the tables is sorted. Les performances des requêtes sont meilleures car l'optimiseur de requête n'a pas besoin d'effectuer de validation supplémentaire pour localiser et mettre à jour les lignes en double, et aucune opération de tri supplémentaire n'est nécessaire.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.

Meilleures pratiques pour les jointuresJOIN Best Practices

Pour améliorer les performances de l'instruction MERGE et garantir l'obtention de résultats corrects, nous vous recommandons de respecter les règles suivantes relatives aux jointures :To improve the performance of the MERGE statement and ensure correct results are obtained, we recommend the following join guidelines:

  • Spécifiez uniquement dans la clause ON <merge_search_condition> des conditions de recherche qui déterminent les critères de correspondance des données dans les tables sources et cibles.Specify only search conditions in the ON <merge_search_condition> clause that determine the criteria for matching data in the source and target tables. Autrement dit, spécifiez uniquement les colonnes de la table cible qui seront comparées aux colonnes correspondantes de la table source.That is, specify only columns from the target table that are compared to the corresponding columns of the source table.
  • N'incluez pas de comparaisons avec d'autres valeurs (par exemple, une constante).Do not include comparisons to other values such as a constant.

Pour filtrer des lignes des tables sources ou cibles, utilisez l’une des méthodes suivantes.To filter out rows from the source or target tables, use one of the following methods.

  • Spécifiez la condition de recherche destinée au filtrage des lignes dans la clause WHEN appropriée.Specify the search condition for row filtering in the appropriate WHEN clause. Par exemple, WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT....For example, WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT....
  • Définissez une vue sur la source ou la cible qui retourne les lignes filtrées et référencez la vue en tant que table source ou cible.Define a view on the source or target that returns the filtered rows and reference the view as the source or target table. Si la vue est définie sur la table cible, toutes les opérations dont elle fait l'objet doivent satisfaire aux conditions requises pour la mise à jour des vues.If the view is defined on the target table, any actions against it must satisfy the conditions for updating views. Pour plus d’informations sur la mise à jour de données à l’aide d’un affichage, consultez Modification des données via un affichage.For more information about updating data by using a view, see Modifying Data Through a View.
  • Utilisez la clause WITH <common table expression> pour filtrer les lignes des tables sources ou cibles.Use the WITH <common table expression> clause to filter out rows from the source or target tables. Cette méthode est semblable à la spécification de critères de recherche supplémentaires dans la clause ON et peut produire des résultats incorrects.This method is similar to specifying additional search criteria in the ON clause and may produce incorrect results. Nous vous recommandons d'éviter l'utilisation de cette méthode ou de la tester soigneusement avant de l'implémenter.We recommend that you avoid using this method or test thoroughly before implementing it.

L'opération de jointure dans l'instruction MERGE peut être optimisée de la même façon qu'une jointure dans une instruction SELECT.The join operation in the MERGE statement is optimized in the same way as a join in a SELECT statement. Lorsque SQL Server procède au traitement des jointures, l’optimiseur de requête choisit parmi plusieurs possibilités la méthode de traitement la plus efficace.That is, when SQL Server processes joins, the query optimizer chooses the most efficient method (out of several possibilities) of processing the join. Lorsque la source et la cible sont de taille semblable et que vous avez appliqué aux tables sources et cibles les lignes directrices relatives aux index décrites précédemment, un opérateur de jointure de fusion constitue le plan de requête le plus efficace.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. En effet, les deux tables ne sont analysées qu'une fois et il n'est pas nécessaire de trier les données.This is because both tables are scanned once and there is no need to sort the data. Lorsque la source est plus petite que la table cible, un opérateur de boucles imbriquées est préférable.When the source is smaller than the target table, a nested loops operator is preferable.

Vous pouvez forcer l'utilisation d'une jointure spécifique en spécifiant la clause OPTION (<query_hint>) dans l'instruction MERGE.You can force the use of a specific join by specifying the OPTION (<query_hint>) clause in the MERGE statement. Nous vous recommandons de ne pas utiliser de jointure de hachage comme indicateur de requête pour les instructions MERGE car ce type de jointure n'utilise pas d'index.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.

Meilleures pratiques pour le paramétrageParameterization Best Practices

Si une instruction SELECT, INSERT, UPDATE ou DELETE est exécutée sans paramètres, l’optimiseur de requête SQL Server peut choisir de paramétrer l’instruction en interne.If a SELECT, INSERT, UPDATE, or DELETE statement is executed without parameters, the SQL Server query optimizer may choose to parameterize the statement internally. Toute valeur littérale contenue dans la requête est alors remplacée par des paramètres.This means that any literal values that are contained in the query are substituted with parameters. Par exemple, l’instruction insère dbo. Les valeurs MyTable (col1, col2) (1, 10) peuvent être implémentées en interne sous la forme INSERT dbo. VALEURS MyTable (col1, col2) (@p1, @p2).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). Ce processus, appelé paramétrage simple, augmente la capacité du moteur relationnel à faire correspondre les nouvelles instructions SQL aux plans d’exécution existants compilés précédemment.This process, called simple parameterization, increases the ability of the relational engine to match new SQL statements with existing, previously-compiled execution plans. On peut ainsi améliorer les performances des requêtes grâce à la diminution de la fréquence des compilations et recompilations des requêtes.Query performance may be improved because the frequency of query compilations and recompilations are reduced. L'optimiseur de requête n'applique pas le processus de paramétrage simple aux instructions MERGE.The query optimizer does not apply the simple parameterization process to MERGE statements. Par conséquent, les instructions MERGE qui contiennent des valeurs littérales peuvent ne pas s'exécuter de façon aussi fluide que les instructions individuelles INSERT, UPDATE ou DELETE car un nouveau plan est compilé à chaque exécution de l'instruction MERGE.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.

Pour améliorer les performances des requêtes, nous vous recommandons de respecter les règles suivantes relatives au paramétrage :To improve query performance, we recommend the following parameterization guidelines:

  • Paramétrez toutes les valeurs littérales de la clause ON <merge_search_condition> et des clauses WHEN de l’instruction MERGE.Parameterize all literal values in the ON <merge_search_condition> clause and in the the WHEN clauses of the MERGE statement. Par exemple, vous pouvez incorporer l'instruction MERGE dans une procédure stockée pour remplacer les valeurs littérales par les paramètres d'entrée appropriés.For example, you can incorporate the MERGE statement into a stored procedure replacing the literal values with appropriate input parameters.
  • Si vous ne pouvez pas paramétrer l’instruction, créez un repère de plan de type TEMPLATE et spécifiez l’indicateur de requête PARAMETERIZATION FORCED dans le repère de plan.If you cannot parameterize the statement, create a plan guide of type TEMPLATE and specify the PARAMETERIZATION FORCED query hint in the plan guide.
  • Si les instructions MERGE sont fréquemment exécutées sur la base de données, vous devez envisager de définir l'option PARAMETERIZATION sur FORCED.If MERGE statements are executed frequently on the database, consider setting the PARAMETERIZATION option on the database to FORCED. Configurez cette option avec précaution.Use caution when setting this option. L’option PARAMETERIZATION est un paramètre de niveau base de données qui affecte le traitement de toutes les requêtes effectuées sur la base de données.The PARAMETERIZATION option is a database-level setting and affects how all queries against the database are processed.

Meilleures pratiques pour la clause TOPTOP Clause Best Practices

Dans l'instruction MERGE, la clause TOP définit le nombre ou le pourcentage de lignes affectées après la jointure de la table source et de la table cible, et après la suppression des lignes qui ne sont pas éligibles pour une opération de type INSERT, UPDATE ou DELETE.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. La clause TOP réduit le nombre de lignes jointes à la valeur spécifiée et les actions INSERT, UPDATE ou DELETE sont appliquées aux lignes jointes restantes sans respecter un ordre particulier.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. Les lignes ne sont donc pas réparties selon un ordre particulier dans le cadre des actions définies dans les clauses WHEN.That is, there is no order in which the rows are distributed among the actions defined in the WHEN clauses. Par exemple, la spécification de la clause TOP (10) affecte 10 lignes, dont 7 peuvent être mises à jour et 3 insérées, ou alors 1 ligne peut être supprimée, 5 mises à jour et 4 insérées, et ainsi de suite.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.

On utilise couramment la clause TOP pour effectuer des opérations en langage DML sur une grande table dans les lots.It is common to use the TOP clause to perform data manipulation language (DML) operations on a large table in batches. Lorsque l'on utilise dans ce but la clause TOP dans l'instruction MERGE, il est important de tenir compte des conséquences suivantes.When using the TOP clause in the MERGE statement for this purpose, it is important to understand the following implications.

  • Les performances d'E/S peuvent être affectées.I/O performance may be affected.

    L'instruction MERGE effectue une analyse complète des tables sources et cibles.The MERGE statement performs a full table scan of both the source and target tables. La division de l'opération en lots permet de réduire le nombre d'opérations d'écriture effectuées par lot. Toutefois, chaque lot effectuera une analyse complète des tables sources et cibles.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. L'activité de lecture résultante peut affecter les performances de la requête.The resulting read activity may affect the performance of the query.

  • Des résultats incorrects peuvent se produire.Incorrect results can occur.

    Il est important de s’assurer que tous les lots consécutifs traitent les nouvelles lignes. Dans le cas contraire, des comportements indésirables tels que l’insertion incorrecte de lignes en double dans la table cible peuvent se produire.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. Cela peut se produire lorsque la table source inclut une ligne que ne figurait pas dans un lot cible mais figurait dans la table cible globale.This can happen when the source table includes a row that was not in a target batch but was in the overall target table.

  • Pour garantir l'obtention de résultats corrects :To insure correct results:

    • Utilisez la clause ON pour déterminer quelles lignes sources affectent les lignes cibles existantes et quelles lignes sont vraiment nouvelles.Use the ON clause to determine which source rows affect existing target rows and which are genuinely new.
    • Utilisez une condition supplémentaire dans la clause WHEN MATCHED pour déterminer si la ligne cible a déjà été mise à jour par un lot précédent.Use an additional condition in the WHEN MATCHED clause to determine if the target row has already been updated by a previous batch.

Étant donné que la clause TOP n'est appliquée qu'une fois ces clauses appliquées, chaque exécution insère une ligne réellement sans correspondance ou met à jour une ligne existante.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.

Meilleures pratiques pour le chargement en masseBulk Load Best Practices

L’instruction MERGE peut être utilisée pour effectuer un chargement en masse efficace des données à partir d’un fichier de données sources vers une table cible en spécifiant la clause OPENROWSET(BULK…) comme source de table.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. La totalité du fichier est ainsi traitée dans un lot unique.By doing so, the entire file is processed in a single batch.

Pour améliorer les performances du processus de fusion en masse, nous vous recommandons de respecter les règles suivantes :To improve the performance of the bulk merge process, we recommend the following guidelines:

  • Créez un index cluster sur les colonnes de jointure de la table cible.Create a clustered index on the join columns in the target table.

  • Utilisez les conseils ORDER et UNIQUE dans la clause OPENROWSET(BULK…) pour spécifier la façon dont le fichier de données sources a été trié.Use the ORDER and UNIQUE hints in the OPENROWSET(BULK…) clause to specify how the source data file is sorted.

    Par défaut, le processus de chargement en masse considère que le fichier de données n'est pas trié.By default, the bulk operation assumes the data file is unordered. Par conséquent, il est important que les données sources soient triées d'après l'index cluster sur la table cible et que l'indicateur ORDER soit utilisé pour indiquer l'ordre de tri afin que l'optimiseur de requête puisse générer un plan de requête plus efficace.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. Les indicateurs sont validés pendant l'exécution. Si le flux de données ne se conforme pas aux indicateurs spécifiés, une erreur est générée.Hints are validated at runtime; if the data stream does not conform to the specified hints, an error is raised.

Le respect de ces règles permet d'être sûr que les clés de jointure sont uniques et que l'ordre de tri des données dans le fichier source correspond à celui de la table cible.These guidelines ensure that the join keys are unique and the sort order of the data in the source file matches the target table. Les performances des requêtes sont améliorées car aucune opération de tri supplémentaire n'est nécessaire et aucune copie des données n'est requise.Query performance is improved because additional sort operations are not necessary and unnecessary data copies are not required.

Mesure et diagnostic des performances de l'instruction MERGEMeasuring and Diagnosing MERGE Performance

Les fonctionnalités suivantes sont à votre disposition pour vous aider à mesurer et diagnostiquer les performances des instructions MERGE.The following features are available to assist you in measuring and diagnosing the performance of MERGE statements.

  • Utilisez le compteur merge stmt dans la vue de gestion dynamique sys.dm_exec_query_optimizer_info pour retourner le nombre d’optimisations de requêtes concernant les instructions MERGE.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.
  • Utilisez l’attribut merge_action_type dans la vue de gestion dynamique sys.dm_exec_plan_attributes pour retourner le type de plan d’exécution de déclencheur utilisé comme résultat d’une instruction MERGE.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.
  • Utilisez Trace SQL pour rassembler des données de résolution des problèmes pour l'instruction MERGE comme vous le feriez pour d'autres instructions en langage DML.Use SQL Trace to gather troubleshooting data for the MERGE statement in the same way you would for other data manipulation language (DML) statements. Pour en savoir plus, voir SQL Trace.For more information, see SQL Trace.

ExemplesExamples

R.A. Utilisation de MERGE pour effectuer des opérations INSERT et UPDATE sur une table dans une instruction uniqueUsing MERGE to do INSERT and UPDATE operations on a table in a single statement

Un scénario courant est la mise à jour d’une ou de plusieurs colonnes dans une table si une ligne correspondante existe.A common scenario is updating one or more columns in a table if a matching row exists. Ou l’insertion des données en tant que nouvelle ligne si une ligne correspondante n’existe pas.Or, inserting the data as a new row if a matching row doesn't exist. En général, vous exécutez l’un des scénarios en passant des paramètres à une procédure stockée qui contient les instructions UPDATE et INSERT appropriées.You usually do either scenario by passing parameters to a stored procedure that contains the appropriate UPDATE and INSERT statements. Avec l'instruction MERGE, vous pouvez effectuer les deux tâches dans une instruction unique.With the MERGE statement, you can do both tasks in a single statement. L'exemple suivant illustre une procédure stockée qui contient à la fois une instruction INSERT et une instruction UPDATE dans la base de données AdventureWorks2012AdventureWorks2012.The following example shows a stored procedure in the AdventureWorks2012AdventureWorks2012database that contains both an INSERT statement and an UPDATE statement. La procédure est ensuite modifiée pour exécuter les opérations équivalentes à l'aide d'une seule instruction MERGE.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. Utilisation de MERGE pour effectuer des opérations UPDATE et DELETE sur une table dans une instruction uniqueUsing MERGE to do UPDATE and DELETE operations on a table in a single statement

L'exemple suivant utilise la clause MERGE pour mettre quotidiennement à jour la table ProductInventory dans l'exemple de base de données AdventureWorks2012AdventureWorks2012, selon les commandes traitées dans la table SalesOrderDetail.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. La colonne Quantity de la table ProductInventory est mise à jour en soustrayant le nombre de commandes passées chaque jour pour chaque produit dans la table SalesOrderDetail.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. Si le nombre de commandes concernant un produit est tel que le stock de ce produit tombe à 0 ou en dessous de cette valeur, la ligne correspondant à ce produit est supprimée de la table ProductInventory.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. Utilisation de l'instruction MERGE pour effectuer des opérations UPDATE et INSERT sur une table cible à l'aide d'une table source dérivéeUsing MERGE to do UPDATE and INSERT operations on a target table by using a derived source table

L'exemple suivant utilise l'instruction MERGE pour modifier la table SalesReason en mettant à jour ou en insérant des lignes dans la base de données AdventureWorks2012AdventureWorks2012.The following example uses MERGE to modify the SalesReason table in the AdventureWorks2012AdventureWorks2012 database by either updating or inserting rows. Lorsque la valeur de NewName dans la table source correspond à une valeur de la colonne Name dans la table cible, (SalesReason), la colonne ReasonType est mise à jour dans la table cible.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. Lorsque la valeur de NewName ne correspond à aucune autre valeur, la ligne source est insérée dans la table cible.When the value of NewName doesn't match, the source row is inserted into the target table. La table source est une table dérivée qui utilise le constructeur de valeurs de table Transact-SQLTransact-SQL afin de spécifier plusieurs lignes pour la table source.The source table is a derived table that uses the Transact-SQLTransact-SQL table value constructor to specify multiple rows for the source table. Pour plus d’informations sur l’utilisation du constructeur de valeurs de table dans une table dérivée, consultez Constructeur de valeurs de table (Transact-SQL).For more information about using the table value constructor in a derived table, see Table Value Constructor (Transact-SQL). L’exemple montre également comment stocker les résultats de la clause OUTPUT dans une variable de table.The example also shows how to store the results of the OUTPUT clause in a table variable. Puis, vous résumez les résultats de l’instruction MERGE en exécutant une opération de sélection simple qui retourne le nombre de lignes insérées et mises à jour.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. Insertion des résultats de l'instruction MERGE dans une autre tableInserting the results of the MERGE statement into another table

L'exemple suivant capture les données retournées par la clause OUTPUT d'une instruction MERGE et insère ces données dans une autre table.The following example captures data returned from the OUTPUT clause of a MERGE statement and inserts that data into another table. L’instruction MERGE met à jour la colonne Quantity de la table ProductInventory dans la base de données AdventureWorks2012AdventureWorks2012 selon les commandes traitées dans la table SalesOrderDetail.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. L'exemple capture les lignes mises à jour et les insère dans une autre table utilisée pour suivre les modifications de stock.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. Utilisation de MERGE pour effectuer une opération INSERT ou UPDATE sur une table d’arêtes cible dans une base de données de graphesUsing MERGE to do INSERT or UPDATE on a target edge table in a graph database

Dans cet exemple, vous créez des tables de nœuds Person et City et une table d’arêtes livesIn.In this example, you create node tables Person and City and an edge table livesIn. Vous utilisez l’instruction MERGE sur l’arête livesIn et insérez une nouvelle ligne si l’arête n’existe pas encore entre une Person et City.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. Si l’arête existe déjà, vous mettez simplement à jour l’attribut StreetAddress sur l’arête livesIn.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

 Voir aussiSee Also