FUSION (Transact-SQL)

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics (pool SQL dédié uniquement)Base de données SQL dans Microsoft FabricEntrepôt dans Microsoft Fabric

L’instruction MERGE 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. 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.

Cet article fournit différentes syntaxes, arguments, remarques, autorisations et exemples basés sur la version de produit sélectionnée. Sélectionnez la version de votre produit souhaitée dans la liste déroulante des versions.

Conventions de la syntaxe Transact-SQL

Syntax

Syntaxe pour SQL Server et Azure SQL Database :

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

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

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

<merge_search_condition> ::=
    <search_condition>

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

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

<clause_search_condition> ::=
    <search_condition>

Arguments

AVEC <common_table_expression>

Spécifie le jeu de résultats ou la vue nommé temporaire, également appelé expression de table commune, définie dans l’étendue de l’instruction MERGE . Le jeu de résultats dérive d’une requête simple et est référencé par l’instruction MERGE . Pour plus d’informations, consultez WITH common_table_expression (Transact-SQL).

HAUT ( expression ) [ PERCENT ]

Spécifie le nombre ou le pourcentage de lignes affectées. L’argument expression peut être un nombre ou un pourcentage de lignes. Les lignes référencées dans l’expression TOP ne sont pas organisées dans un ordre quelconque. Pour plus d’informations, consultez TOP (Transact-SQL).

La TOP clause s’applique après l’intégralité de la table source et la jointure de la table cible entière et les lignes jointes qui ne sont pas éligibles pour une action d’insertion, de mise à jour ou de suppression. La TOP clause réduit davantage le nombre de lignes jointes à la valeur spécifiée. Ces actions (insertion, mise à jour ou suppression) s’appliquent aux lignes jointes restantes de manière non ordonnée. Autrement dit, il n’existe aucun ordre dans lequel les lignes sont distribuées entre les actions définies dans les WHEN clauses. Par exemple, la spécification TOP (10) affecte 10 lignes. Parmi ces lignes, 7 peuvent être mises à jour et 3 insérées, ou 1 ligne peut être supprimée, 5 mises à jour et 4 insérées, et ainsi de suite.

Sans filtres sur la table source, l’instruction MERGE peut effectuer une analyse de table ou une analyse d’index cluster sur la table source, ainsi qu’une analyse de table ou une analyse d’index cluster de la table cible. Par conséquent, les performances des E/S sont parfois affectées même lors de l’utilisation de la TOP clause 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 ciblent les nouvelles lignes.

database_name

Nom de la base de données contenant target_table.

schema_name

Nom du schéma auquel target_table appartient.

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 d’opérations d’insertion, de mise à jour ou de suppression spécifiées par les WHEN clauses de l’instruction MERGE .

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. Pour plus d’informations, consultez Modifier les données par l’intermédiaire d’une vue.

target_table ne peut pas être une table distante. target_table ne peut pas avoir de règles définies dessus. target_table ne peut pas être une table à mémoire optimisée.

Les indicateurs peuvent être spécifiés en tant que <merge_hint>.

[ EN TANT ] table_alias

Autre nom utilisé pour faire référence à une table pour target_table.

UTILISATION de <table_source>

Spécifie la source de données correspondant aux lignes de données dans target_table en fonction de <merge_search_condition>. Le résultat de cette correspondance détermine les actions à entreprendre par les WHEN clauses de l’instruction MERGE . <table_source> peut être une table distante ou une table dérivée qui accède à des tables distantes.

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

[ EN TANT ] table_alias

Autre nom utilisé pour faire référence à une table pour table_source.

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

SUR <merge_search_condition>

Spécifie les conditions de jointure de <table_source> avec target_table afin de déterminer où la correspondance a lieu.

Caution

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 en filtrant les lignes de la table cible dans la ON clause, par exemple, par exemple, en spécifiant AND NOT target_table.column_x = value. Cette approche peut retourner des résultats inattendus et incorrects.

LORSQU’IL EST MIS EN CORRESPONDANCE, PUIS <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 répondent à toute condition de recherche supplémentaire, sont mises à jour ou supprimées conformément à la <merge_matched> clause.

L’instruction MERGE peut avoir, au plus, deux WHEN MATCHED clauses. Si deux clauses sont spécifiées, la première clause doit être accompagnée d’une AND<search_condition> clause. Pour une ligne donnée, la deuxième WHEN MATCHED clause est appliquée uniquement si la première n’est pas. S’il existe deux WHEN MATCHED clauses, il faut spécifier une UPDATE action et une DELETE action doit être spécifiée. Lorsqu’elle UPDATE est spécifiée dans la <merge_matched> clause et que plusieurs lignes de <table_source> correspondances correspondent à une ligne dans target_table basée sur <merge_search_condition>, SQL Server retourne une erreur. L’instruction MERGE ne peut pas mettre à jour la même ligne plusieurs fois, ni mettre à jour et supprimer la même ligne.

LORSQU’IL N’EST PAS MIS EN CORRESPONDANCE [ PAR CIBLE ] PUIS <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> ce 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 ne peut avoir qu’une WHEN NOT MATCHED [ BY TARGET ] seule clause.

LORSQU’IL N’EST PAS MIS EN CORRESPONDANCE PAR SOURCE, 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 à une condition de recherche supplémentaire, sont mises à jour ou supprimées conformément à la <merge_matched> clause.

L’instruction MERGE peut avoir au maximum deux WHEN NOT MATCHED BY SOURCE clauses. Si deux clauses sont spécifiées, la première clause doit être accompagnée d’une AND<clause_search_condition> clause. Pour une ligne donnée, la deuxième WHEN NOT MATCHED BY SOURCE clause est appliquée uniquement si la première n’est pas. S’il existe deux WHEN NOT MATCHED BY SOURCE clauses, il faut spécifier une UPDATE action et une DELETE action. Seules les colonnes de la table cible peuvent être référencées dans <clause_search_condition>.

Quand 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> 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 faire en sorte que l’instruction échoue dans la mesure où Col1 dans la table source est inaccessible.

ET <clause_search_condition>

Spécifie toute condition de recherche valide. Pour plus d’informations, consultez la condition de recherche (Transact-SQL).

<table_hint_limited>

Spécifie un ou plusieurs indicateurs de table à appliquer à la table cible pour chacune des actions d’insertion, de mise à jour ou de suppression effectuées par l’instruction MERGE . Le WITH mot clé et les parenthèses sont requis.

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 INSERT instruction 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 à la table source.

Caution

La spécification READPAST avec WHEN NOT MATCHED [ BY TARGET ] THEN INSERT peut entraîner INSERT des opérations qui violent les UNIQUE contraintes.

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. $action peut être spécifié dans la clause de sortie. $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. La OUTPUT clause est la méthode recommandée pour interroger ou compter les lignes affectées par un MERGE. Pour plus d’informations sur les arguments et le comportement de cette clause, consultez la clause OUTPUT (Transact-SQL).

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

Spécifie que des indicateurs d’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 appliquée à 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 à toute condition de recherche supplémentaire.

MISE À JOUR DU 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.

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 prise en charge.

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 sous la forme d’un nom à composant unique, ou bien l’instruction MERGE échoue. column_list doit être placé entre parenthèses et délimité par des virgules.

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. Les expressions ne peuvent pas contenir d’instruction EXECUTE .

VALEURS PAR DÉFAUT

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

<search_condition>

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

<modèle de recherche de graphique>

Spécifie le modèle de correspondance de graphe. Pour plus d’informations sur les arguments de cette clause, consultez MATCH (Transact-SQL).

Remarks

Le comportement conditionnel décrit pour l’instruction MERGE fonctionne mieux lorsque les deux tables ont un mélange complexe de caractéristiques correspondantes. Par exemple, l'insertion d'une ligne si elle n'existe pas ou la mise à jour d’une ligne si elle correspond. Lorsque vous mettez simplement à jour une table en fonction des lignes d’une autre table, améliorez les performances et l’extensibilité avec INSERT, UPDATEet les DELETE instructions. Par exemple:

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

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

Toute action d’insertion, de mise à jour ou de suppression spécifiée sur la table cible par l’instruction MERGE est limitée par toutes les contraintes définies sur celle-ci, y compris les contraintes d’intégrité référentielle en cascade. S’il IGNORE_DUP_KEY s’agit ON d’index uniques sur la table cible, MERGE ignore ce paramètre.

L’instruction MERGE nécessite un point-virgule (;) comme fin d’instruction. L’erreur 10713 est déclenchée lorsqu’une MERGE instruction est exécutée sans fin.

Lorsqu’il est utilisé après MERGE, @@ROWCOUNT (Transact-SQL) retourne le nombre total de lignes insérées, mises à jour et supprimées pour le client.

MERGE est un mot clé entièrement réservé lorsque le niveau de compatibilité de la base de données est défini 100 sur ou supérieur. L’instruction MERGE est disponible sous les 90 niveaux de compatibilité de base de données et 100 de base de données ; toutefois, le mot clé n’est pas entièrement réservé lorsque le niveau de compatibilité de la base de données est défini 90sur .

Caution

N’utilisez pas l’instruction lors de l’utilisation de la MERGEréplication de mise à jour en file d’attente. Le MERGE déclencheur de mise à jour en file d’attente et le déclencheur ne sont pas compatibles. Remplacez l’instruction MERGE par une INSERT instruction et UPDATE des instructions.

Implémentation du déclencheur

Pour chaque action d’insertion, de mise à jour ou de suppression spécifiée dans l’instruction MERGE , SQL Server déclenche tous les déclencheurs correspondants AFTER définis sur la table cible, mais ne garantit pas l’action à déclencher en premier ou en dernier. Les déclencheurs définis pour la même opération respectent l'ordre que vous spécifiez. 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.

Si la table cible a un déclencheur OF activé INSTEAD défini dessus pour une action d’insertion, de mise à jour ou de suppression effectuée par une MERGE instruction, il doit avoir un déclencheur OF activé INSTEAD pour toutes les actions spécifiées dans l’instruction MERGE .

Si des INSTEAD déclencheurs OF ou UPDATE OF INSTEADDELETE sont définis sur target_table, les opérations de mise à jour ou de suppression ne sont pas exécutées. À la place, les déclencheurs sont lancés et les tables insérées et supprimées sont remplies en conséquence.

Si des INSTEAD déclencheurs OF INSERT sont définis sur target_table, l’opération d’insertion n’est pas effectuée. Au lieu de cela, la table est remplie en conséquence.

Note

Contrairement à des instructions distinctes INSERT, UPDATEet DELETE des instructions, le nombre de lignes reflétées à @@ROWCOUNT l’intérieur d’un déclencheur peut être plus élevé. L’intérieur @@ROWCOUNT de n’importe quel AFTER déclencheur (quelles que soient les instructions de modification de données capturées par le déclencheur) reflète le nombre total de lignes affectées par le MERGE. Par exemple, si une instruction insère une MERGE ligne, met à jour une ligne et supprime une ligne, @@ROWCOUNT est trois pour un AFTER déclencheur, même si le déclencheur est déclaré uniquement pour INSERT les instructions.

Permissions

Nécessite SELECT une autorisation sur la table source et INSERT, UPDATEou DELETE des autorisations sur la table cible. Pour plus d’informations, consultez la section Autorisations dans les articles SELECT (Transact-SQL),INSERT (Transact-SQL), UPDATE (Transact-SQL) et DELETE (Transact-SQL).

Bonnes pratiques pour les index

À l’aide de l’instruction MERGE , vous pouvez remplacer les instructions DML individuelles par une seule instruction. 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. 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.

Pour améliorer les performances de l’instruction MERGE , nous vous recommandons les instructions d’index suivantes :

• Créez des index pour faciliter la jointure entre la source et la cible du MERGE:
  • Créez un index sur les colonnes de jointure de la table source qui contient des clés couvrant la logique de jointure de la table cible. Si possible, il doit être unique.
  • Créez également un index sur les colonnes de jointure de la table cible. Si possible, il doit s’agir d’un index cluster unique.
  • Ces deux index garantissent que les données des tables sont triées et que l’unicité aide les performances de la comparaison. 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.
• Évitez les tables avec n’importe quelle forme d’index columnstore comme cible d’instructions MERGE . Comme avec les UPDATEs, vous pouvez trouver des performances meilleures avec les index columnstore en mettant à jour une table rowstore intermédiaire, puis en effectuant un traitement par lots DELETE et INSERT, au lieu d’un UPDATE ou MERGE.

Considérations sur l’accès concurrentiel pour l’instruction MERGE

En termes de verrouillage, MERGE il diffère des instructions discrètes, consécutivesINSERTUPDATE, et DELETE des instructions. MERGE exécute INSERTtoujours, UPDATEet DELETE les opérations, mais utilisent différents mécanismes de verrouillage. Il peut être plus efficace d’écrire des instructions discrètes INSERT, UPDATEet des DELETE instructions pour certains besoins d’application. À grande échelle, MERGE vous pouvez introduire des problèmes d’accès concurrentiel complexes ou nécessiter une résolution avancée des problèmes. Par conséquent, envisagez de tester soigneusement une MERGE instruction avant de procéder au déploiement en production.

MERGEles instructions sont un remplacement approprié pour les opérations discrètes INSERTUPDATEet DELETE les opérations dans (mais pas limités à) les scénarios suivants :

• Les opérations ETL impliquant des nombres de lignes volumineux doivent être exécutées pendant une période où d’autres opérations simultanées ne sont pas* attendues. Lorsque l’accès concurrentiel lourd est attendu, distinct INSERT, UPDATEet DELETE que la logique peut s’exécuter mieux, avec moins de blocage, qu’une MERGE instruction.
• Opérations complexes impliquant des nombres de lignes réduites et des transactions peu susceptibles de s’exécuter pendant une durée prolongée.
• Les opérations complexes impliquant des tables utilisateur dans lesquelles les index peuvent être conçus pour garantir des plans d’exécution optimaux, évitant les analyses de table et les recherches en faveur d’analyses d’index ou de recherches d’index dans l’idéal.

Autres éléments à prendre en compte pour la concurrence :

• Dans certains scénarios où des clés uniques sont censées être insérées et mises à jour par le MERGE, en spécifiant l’option HOLDLOCK empêchera les violations de clés uniques. HOLDLOCK est un synonyme du SERIALIZABLE niveau d’isolation des transactions, qui n’autorise pas d’autres transactions simultanées à modifier les données lues par cette transaction. SERIALIZABLE est le niveau d’isolation le plus sûr, mais fournit la moindre concurrence avec d’autres transactions qui conservent des verrous sur des plages de données afin d’empêcher l’insertion ou la mise à jour de lignes fantômes pendant que les lectures sont en cours. Pour plus d’informations sur HOLDLOCK, consultez Indicateurs de table et SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

Bonnes pratiques pour JOIN

Pour améliorer les performances de l’instruction MERGE et vérifier que les résultats corrects sont obtenus, nous vous recommandons les instructions de jointure suivantes :

• Spécifiez uniquement les conditions de recherche dans la ON <merge_search_condition> clause qui déterminent les critères de correspondance des données dans les tables source et cible. Autrement dit, spécifiez uniquement les colonnes de la table cible qui seront comparées aux colonnes correspondantes de la table source.
• N’incluez pas de comparaisons avec d’autres valeurs (par exemple, une constante).

Pour filtrer des lignes des tables sources ou cibles, utilisez l’une des méthodes suivantes.

• Spécifiez la condition de recherche pour le filtrage de lignes dans la clause appropriée WHEN . Par exemple, 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. 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. Pour plus d’informations sur la mise à jour des données à l’aide d’une vue, consultez Modifier les données via une vue.
• Utilisez la clause WITH <common table expression> pour filtrer les lignes des tables sources ou cibles. Cette méthode est similaire à la spécification de critères de recherche supplémentaires dans la ON clause et peut produire des résultats incorrects. Nous vous recommandons d'éviter l'utilisation de cette méthode ou de la tester soigneusement avant de l'implémenter.

L’opération de jointure dans l’instruction MERGE est optimisée de la même façon qu’une jointure dans une SELECT instruction. Lorsque SQL Server traite la jointure, l’optimiseur de requête choisit parmi plusieurs possibilités la méthode de traitement la plus efficace. 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. En effet, les deux tables ne sont analysées qu’une fois et il n’est pas nécessaire de trier les données. Lorsque la source est plus petite que la table cible, un opérateur de boucles imbriquées est préférable.

Vous pouvez forcer l’utilisation d’une jointure spécifique en spécifiant la OPTION (<query_hint>) clause dans l’instruction MERGE . Nous vous recommandons de ne pas utiliser la jointure de hachage comme indicateur de requête pour MERGE les instructions, car ce type de jointure n’utilise pas d’index.

Bonnes pratiques pour le paramétrage

Si une SELECTinstruction , ou INSERTUPDATE une DELETEinstruction est exécutée sans paramètres, l’optimiseur de requête SQL Server peut choisir de paramétrer l’instruction en interne. Toute valeur littérale contenue dans la requête est alors remplacée par des paramètres. Par exemple, l’instruction INSERT dbo.MyTable (Col1, Col2) VALUES (1, 10) peut être implémentée en interne sous la forme INSERT dbo.MyTable (Col1, Col2) VALUES (@p1, @p2). Ce processus, appelé paramétrisation simple, augmente la capacité du moteur relationnel à faire correspondre les nouvelles instructions SQL aux plans d’exécution existants compilés précédemment. 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. L’optimiseur de requête n’applique pas le processus de paramétrage simple aux MERGE instructions. Par conséquent, MERGE les instructions qui contiennent des valeurs littérales peuvent ne pas être exécutées et individuelles, INSERTou UPDATE des instructions, DELETEcar un nouveau plan est compilé chaque fois que l’instruction MERGE est exécutée.

Pour améliorer les performances des requêtes, nous vous recommandons de respecter les règles suivantes relatives au paramétrage :

• Paramétrez toutes les valeurs littérales dans la ON <merge_search_condition> clause et dans les WHEN clauses de l’instruction MERGE . Par exemple, vous pouvez incorporer l’instruction MERGE dans une procédure stockée en remplaçant les valeurs littérales par les paramètres d’entrée appropriés.
• 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. Pour plus d’informations, consultez Spécifier le comportement du paramétrage de requêtes grâce aux repères de plan.
• Si MERGE les instructions sont exécutées fréquemment sur la base de données, envisagez de définir l’option PARAMETERIZATION sur la base de données FORCEDsur . Configurez cette option avec précaution. 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. Pour plus d'informations, consultez Paramétrage forcé.
• Comme alternative plus récente et plus simple aux repères de plan, envisagez une stratégie similaire avec des indicateurs du Magasin des requêtes. Pour plus d’informations, consultez Indicateurs du Magasin des requêtes.

Meilleures pratiques pour la clause TOP

Dans l’instruction MERGE , la TOP clause spécifie le nombre ou le pourcentage de lignes affectées après la table source et la table cible sont jointes, et après les lignes qui ne sont pas éligibles pour une action d’insertion, de mise à jour ou de suppression sont supprimées. La TOP clause réduit davantage le nombre de lignes jointes à la valeur spécifiée et les actions d’insertion, de mise à jour ou de suppression sont appliquées aux lignes jointes restantes de manière non ordonnée. Autrement dit, il n’existe aucun ordre dans lequel les lignes sont distribuées entre les actions définies dans les WHEN clauses. Par exemple, la spécification TOP (10) affecte 10 lignes ; de ces lignes, 7 peuvent être mises à jour et 3 insérées, ou 1 peut être supprimée, 5 mises à jour et 4 insérées, et ainsi de suite.

Il est courant d’utiliser la TOP clause pour effectuer des opérations de langage de manipulation de données (DML) sur une table volumineuse par lots. Lorsque vous utilisez la TOP clause dans l’instruction MERGE à cet effet, il est important de comprendre les implications suivantes.

• Les performances d’E/S peuvent être affectées.

  L’instruction MERGE effectue une analyse complète des tables sources et cibles. 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 de table complète des tables sources et cibles. L’activité de lecture résultante peut affecter les performances de la requête et d’autres activités simultanées sur les tables.

• Des résultats incorrects peuvent se produire.

  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. 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. Pour garantir l’obtention de résultats corrects :

  • Utilisez la ON clause pour déterminer quelles lignes sources affectent les lignes cibles existantes et qui sont réellement nouvelles.
  • Utilisez une condition supplémentaire dans la WHEN MATCHED clause pour déterminer si la ligne cible a déjà été mise à jour par un lot précédent.
  • Utilisez une condition supplémentaire dans la clause et WHEN MATCHED la SET logique pour vérifier que la même ligne ne peut pas être mise à jour deux fois.

Étant donné que la TOP clause est appliquée uniquement après l’application de ces clauses, chaque exécution insère une ligne véritablement sans correspondance ou met à jour une ligne existante.

Meilleures pratiques pour le chargement en masse

L’instruction MERGE peut être utilisée pour charger efficacement des données à partir d’un fichier de données source dans une table cible en spécifiant la OPENROWSET(BULK...) clause comme source de table. La totalité du fichier est ainsi traitée dans un lot unique.

Pour améliorer les performances du processus de fusion en masse, nous vous recommandons de respecter les règles suivantes :

• Créez un index cluster sur les colonnes de jointure de la table cible.

• Désactivez d’autres index non uniques et non cluster sur la table cible pendant le chargement MERGEen bloc, puis activez-les par la suite. Cela est courant et utile pour les opérations de données en bloc nocturnes.

• Utilisez les indicateurs et ORDER les UNIQUE indicateurs de la OPENROWSET(BULK...) clause pour spécifier la façon dont le fichier de données source est trié.

  Par défaut, le processus de chargement en masse considère que le fichier de données n'est pas trié. Par conséquent, il est important que les données sources soient triées en fonction de l’index cluster sur la table cible et que l’indicateur ORDER est utilisé pour indiquer l’ordre afin que l’optimiseur de requête puisse générer un plan de requête plus efficace. 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.

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

Mesurer et diagnostiquer les performances MERGE

Les fonctionnalités suivantes sont disponibles pour vous aider à mesurer et diagnostiquer les performances des MERGE instructions.

• Utilisez le compteur stmt de fusion dans la vue de gestion dynamique sys.dm_exec_query_optimizer_info pour retourner le nombre d’optimisations de requête destinées aux MERGE instructions.
• 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 du déclencheur utilisé à la suite d’une MERGE instruction.
• Utilisez une session d’événements étendus pour collecter des données de résolution des problèmes pour l’instruction MERGE de la même façon que pour d’autres instructions DML (Data Manipulation Language). Pour plus d’informations sur l’aperçu des événements étendus, consultez Démarrage rapide : événements étendus et Utiliser SSMS XEvent Profiler.

Examples

A. Utiliser MERGE pour effectuer des opérations INSERT et UPDATE sur une table dans une instruction unique

Un scénario courant est la mise à jour d’une ou de plusieurs colonnes dans une table si une ligne correspondante existe. Ou l’insertion des données en tant que nouvelle ligne si une ligne correspondante n’existe pas. Vous effectuez généralement l’un ou l’autre scénario en passant des paramètres à une procédure stockée qui contient les instructions appropriéesUPDATE.INSERT Avec l’instruction MERGE , vous pouvez effectuer les deux tâches dans une seule instruction. L’exemple suivant montre une procédure stockée dans la base de données AdventureWorks2025 qui contient à la fois une INSERT instruction et une UPDATE instruction. La procédure est ensuite modifiée pour exécuter les opérations équivalentes à l’aide d’une instruction unique MERGE .

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

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

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

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

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

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

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

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

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

SELECT * FROM #MyTempTable;

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

DROP TABLE #MyTempTable;
GO

B. Utiliser MERGE pour effectuer des opérations UPDATE et DELETE sur une table dans une instruction unique

L’exemple suivant sert MERGE à mettre à jour quotidiennement la ProductInventory table dans la base de données d’exemples AdventureWorks2025, en fonction des commandes traitées dans la 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. 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.

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

EXECUTE Production.usp_UpdateInventory '20030501';

C. Utiliser l’instruction MERGE pour effectuer des opérations UPDATE et INSERT sur une table cible à l’aide d’une table source dérivée

L’exemple suivant sert MERGE à modifier la SalesReason table dans la base de données AdventureWorks2025 en mettant à jour ou en insérant des lignes.

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. Lorsque la valeur de NewName ne correspond à aucune autre valeur, la ligne source est insérée dans la table cible. La table source est une table dérivée qui utilise le constructeur de valeurs de table Transact-SQL afin de spécifier plusieurs lignes pour la table source. 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).

La OUTPUT clause peut être utile pour interroger le résultat des instructions, pour plus d’informations MERGE , consultez la clause OUTPUT (Transact-SQL). L’exemple montre également comment stocker les résultats de la OUTPUT clause dans une variable de table. Vous résumez ensuite 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.

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

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

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

D. Insérer les résultats de l’instruction MERGE dans une autre table

L’exemple suivant capture les données retournées à partir de la OUTPUT clause d’une MERGE instruction et insère ces données dans une autre table. L’instruction MERGE met à jour la Quantity colonne du ProductInventory tableau dans la base de données AdventureWorks2025, en fonction des commandes traitées dans la 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.

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

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

E. Utiliser MERGE pour effectuer une opération INSERT ou UPDATE sur une table d’arêtes cible dans une base de données de graphe

Dans cet exemple, vous créez des tables de nœuds Person et City et une table d’arêtes livesIn. Vous utilisez l’instruction MERGE sur le livesIn bord et insérez une nouvelle ligne si le bord n’existe pas déjà entre a Person et City. Si l’arête existe déjà, vous mettez simplement à jour l’attribut StreetAddress sur l’arête livesIn.

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

Contenu connexe

• SELECT (Transact-SQL)
• INSÉRER (Transact-SQL)
• MISE À JOUR (Transact-SQL)
• SUPPRIMER (Transact-SQL)
• Clause OUTPUT (Transact-SQL)
• MERGE dans les packages Integration Services
• Clause FROM plus JOIN, APPLY, PIVOT (Transact-SQL)
• Constructeur de valeurs de table (Transact-SQL)