MERGE (Transact-SQL)

Syntaxe

[ 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> ::=
        { [ NOT ] <predicate> | ( <search_condition> ) } 
        [ { AND | OR } [ NOT ] { <predicate> | ( <search_condition> ) } ] 
[ ,...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 ) } 

<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

Arguments

• 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 la portée de l'instruction MERGE. Le jeu de résultats est dérivé d'une simple requête et est référencé par l'instruction MERGE. Pour plus d'informations, consultez WITH common_table_expression (Transact-SQL).

• TOP ( expression ) [ PERCENT ]
  Spécifie le nombre ou le pourcentage de lignes affectées. expression peut être soit un nombre, soit un pourcentage de lignes. Les lignes référencées dans l'expression TOP ne sont pas triées dans un ordre donné. Pour plus d'informations, consultez 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. 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. Les lignes ne sont donc pas réparties selon un ordre particulier dans le cadre des actions définies dans les clauses WHEN. 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.

  É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. Dans ce scénario, il est important de s'assurer que tous les lots consécutifs traitent les nouvelles lignes. Pour plus d'informations, consultez Optimisation des performances de l'instruction MERGE.

• database_name
  Nom de la base de données contenant target_table.

• schema_name
  Nom du schéma auquel appartient target_table.

• target_table
  Table ou vue à laquelle les lignes de données de <table_source> sont comparées sur la base de <clause_search_condition>. target_table est la cible de toute opération INSERT, UPDATE ou DELETE spécifiée par les clauses WHEN de l'instruction MERGE.

  Si target_table est une vue, toutes les actions dont elle fait l'objet doivent satisfaire aux conditions requises pour la mise à jour des vues. Pour plus d'informations, consultez Modification de données par l'intermédiaire d'une vue.

  target_table ne peut pas être une table distante. Aucune règle ne peut être définie sur target_table.

• [ AS ] table_alias
  Autre nom utilisé pour faire référence à une table.

• USING <table_source>
  Spécifie la source de données correspondant aux lignes de données dans target_table selon <merge_search condition>. Le résultat de cette correspondance dicte les actions à entreprendre par les clauses WHEN de l'instruction MERGE. <table_source> peut être une table distante ou une table dérivée qui accède aux tables distantes.

  <table_source> peut être une table dérivée qui utilise le constructeur de valeurs de tableTransact-SQL pour construire une table en spécifiant plusieurs lignes.

  Pour plus d'informations sur la syntaxe et les arguments de cette clause, consultez FROM (Transact-SQL).

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

  Attention

  Il est important de spécifier uniquement les colonnes de la table cible utilisées à des fins de correspondance. Autrement dit, spécifiez les colonnes de la table cible qui seront comparées à la colonne correspondante de la table source. N'essayez pas d'améliorer les performances des requêtes filtrant des lignes de la table cible dans la clause ON, par exemple en spécifiant AND NOT target_table.column_x = value. Cette approche peut retourner des résultats inattendus et incorrects.

• 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 conditions de recherche supplémentaires, sont mises à jour ou supprimées selon la clause <merge_matched>.

  L'instruction MERGE peut avoir au plus deux clauses WHEN MATCHED. Si deux clauses sont spécifiées, la première clause doit être accompagnée d'une clause AND <search_condition>. Pour toute ligne donnée, la deuxième clause WHEN MATCHED est appliquée uniquement si la première ne l'est pas. En présence de deux clauses WHEN MATCHED, l'une d'elles doit spécifier une action UPDATE et l'autre une action DELETE. Si 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 selon <merge_search_condition>, SQL Server retourne une erreur. 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.

• 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 à une condition de recherche supplémentaire, le cas échéant. Les valeurs à insérer sont spécifiées par la clause <merge_not_matched>. L'instruction MERGE peut avoir une seule clause WHEN NOT 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 conditions de recherche supplémentaires, sont mises à jour ou supprimées selon la clause <merge_matched>.

  L'instruction MERGE peut avoir au plus deux clauses WHEN NOT MATCHED BY SOURCE. Si deux clauses sont spécifiées, la première clause doit être accompagnée d'une clause AND <clause_search_condition>. 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. 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. Seules les colonnes de la table cible peuvent être référencées dans <clause_search_condition>.

  Lorsqu'aucune ligne n'est retournée par <table_source>, les colonnes de la table source ne sont pas accessibles. 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. Par exemple, la clause WHEN NOT MATCHED BY SOURCE THEN UPDATE SET TargetTable.Col1 = SourceTable.Col1 peut entraîner l'échec de l'instruction dans la mesure où Col1 dans la table source est inaccessible.

• AND <clause_search_condition>
  Spécifie toute condition de recherche valide. Pour plus d'informations, consultez Condition de recherche (Transact-SQL).

• <table_hint_limited>
  Spécifie un ou plusieurs indicateurs de table qui sont appliqués à la table cible pour chaque action INSERT, UPDATE ou DELETE exécutée par l'instruction MERGE. Le mot clé WITH et les parenthèses sont obligatoires.

  NOLOCK et READUNCOMMITTED ne sont pas autorisés. Pour plus d'informations sur les indicateurs de table, consultez Indicateurs de table (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. Un verrou exclusif est appliqué à la table. Lorsque FORCESEEK est spécifié, il s'applique à l'instance implicite de la table cible jointe avec la table source.

  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.

• 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. Pour plus d'informations, consultez Indicateurs de table (Transact-SQL).

• <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. Pour plus d'informations sur les arguments de cette clause, consultez Clause OUTPUT (Transact-SQL).

• 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. Pour plus d'informations, consultez Indicateurs de requête (Transact-SQL).

• <merge_matched>
  Spécifie l'action de mise à jour ou de 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.

  • UPDATE SET <set_clause>
    Spécifie la liste de noms de colonnes ou de variables à mettre à jour dans la table cible et les valeurs avec lesquelles les mettre à jour.

    Pour plus d'informations sur les arguments de cette clause, consultez UPDATE (Transact-SQL). La définition d'une variable à la même valeur qu'une colonne n'est pas autorisée.

  • DELETE
    Spécifie que les lignes qui correspondent aux lignes dans target_table sont supprimées.

• <merge_not_matched>
  Spécifie les valeurs à insérer dans la table cible.

  • ( column_list )
    Liste d'une ou de plusieurs colonnes de la table cible dans lesquelles insérer des données. Les colonnes doivent être spécifiées comme un nom en une seule partie sinon l'instruction MERGE échouera. column_list doit être mise entre parenthèses et délimitée par des virgules.

  • VALUES ( values_list )
    Liste séparée par des virgules et contenant des constantes, variables ou expressions qui retournent les valeurs à insérer dans la table cible. Les expressions ne peuvent pas contenir d'instruction EXECUTE.

  • DEFAULT VALUES
    Force la ligne insérée à prendre les valeurs par défaut définies pour chaque colonne.

  Pour plus d'informations sur cette clause, consultez INSERT (Transact-SQL).

• <condition de recherche>
  Spécifie les conditions de recherche utilisées pour spécifier <merge_search_condition> ou <clause_search_condition>. Pour plus d'informations sur les arguments pour cette clause, consultez Condition de recherche (Transact-SQL).

Notes

Au moins l'une des trois clauses MATCHED doit être spécifiée, mais cela peut être dans n'importe quel ordre. Une variable ne peut pas être mise à jour plus d'une fois dans la même clause MATCHED.

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. Si IGNORE_DUP_KEY a la valeur ON pour un index unique sur la table cible, MERGE ignore ce paramètre.

L'instruction MERGE requiert un point-virgule (;) comme terminateur d'instruction. L'erreur 10713 est générée lorsqu'une instruction MERGE est exécutée sans le terminateur.

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.

MERGE est un mot clé entièrement réservé lorsque le niveau de compatibilité de la base de données a la valeur 100. 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.

Implémentation de déclencheur

Autorisations

Requiert l'autorisation SELECT sur la table source et les autorisations INSERT, UPDATE ou DELETE sur la table cible. Pour plus d'informations, consultez la section Autorisations dans les rubriques SELECT, INSERT, UPDATE et DELETE.

Exemples

A. Utilisation de MERGE pour effectuer des opérations INSERT et UPDATE sur une table en une seule instruction

USE AdventureWorks;
GO
CREATE PROCEDURE dbo.InsertUnitMeasure
    @UnitMeasureCode nchar(3),
    @Name nvarchar(25)
AS 
BEGIN
    SET NOCOUNT ON;
-- Update the row if it exists.    
    UPDATE Production.UnitMeasure
    SET Name = @Name
    WHERE UnitMeasureCode = @UnitMeasureCode
-- Insert the row if the UPDATE statement failed.   
    IF (@@ROWCOUNT = 0 )
    BEGIN
        INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name)
        VALUES (@UnitMeasureCode, @Name)
    END
END;
GO
-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Test Value';
SELECT UnitMeasureCode, Name FROM Production.UnitMeasure
WHERE UnitMeasureCode = 'ABC';
GO

-- Rewrite the procedure to perform the same operations using the MERGE statement.
-- 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. Utilisation de MERGE pour effectuer des opérations UPDATE et DELETE sur une table en une seule instruction

USE AdventureWorks;
GO
IF OBJECT_ID (N'Production.usp_UpdateInventory', N'P') IS NOT NULL DROP PROCEDURE Production.usp_UpdateInventory;
GO
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. 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ée

USE AdventureWorks;
GO
-- 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. Insertion des résultats de l'instruction MERGE dans une autre table

USE AdventureWorks;
GO
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

Voir aussi

Référence

Concepts