Introduction à l’instruction MERGE et à la modification des données SQL Server
L’instruction MERGE est utilisée pour apporter des modifications dans une table en fonction des valeurs mises en correspondance avec l’anther. Il peut être utilisé pour combiner les opérations d’insertion, de mise à jour et de suppression en une seule instruction. Dans cet article, nous allons découvrir comment utiliser l’instruction MERGE. Nous abordons quelques bonnes pratiques, limitations et récapitulons avec plusieurs exemples.
Ceci est le cinquième article d’une série d’articles. Vous pouvez commencer au début en lisant Introduction aux instructions de modification de données SQL Server.
Tous les exemples de cette leçon sont basés sur Microsoft SQL Server Management Studio et la base de données AdventureWorks2012. Vous pouvez commencer à utiliser ces outils gratuits en utilisant mon Guide Premiers pas avec SQL Server
Avant de commencer
Bien que cet article utilise la base de données AdventureWorks pour ses exemples, j’ai décidé de créer plusieurs exemples de tableaux à utiliser dans la base de données pour mieux illustrer les concepts couverts. Vous pouvez trouver le script dont vous aurez besoin pour exécuter ici. Remarquez qu’il existe une section spéciale relative à MERGE.
Structure de base
L’instruction MERGE combine les opérations INSERT, DELETE et UPDATE en une seule table. Une fois que vous aurez compris comment cela fonctionne, vous verrez que cela simplifie la procédure en utilisant les trois instructions séparément pour synchroniser les données.
Voici un format généralisé pour l’instruction de fusion.
MERGE targetTableUsing sourceTableON mergeConditionWHEN MATCHEDTHEN updateStatementWHEN NOT MATCHED BY TARGETTHEN insertStatementWHEN NOT MATCHED BY SOURCETHEN deleteStatement
L’instruction de fusion fonctionne à l’aide de deux tables, la sourceTable et la targetTable. La targetTable est la table à modifier en fonction des données contenues dans la sourceTable.
Les deux tables sont comparées à l’aide d’une mergeCondition . Cette condition spécifie comment les lignes de la table source sont mises en correspondance avec la table cible. Si vous êtes familier avec INNER JOINS, vous pouvez considérer cela comme la condition de jointure utilisée pour faire correspondre les lignes.
En général, vous correspondriez à un identifiant unique, comme une clé primaire. Si la table source était NewProduct et ProductMaster cible et la clé primaire pour les deux ProductID, une bonne condition de fusion à utiliser serait:
NewProduct.ProductID = ProductMaster.ProductID
Une condition de fusion en résulte dans l’un des trois états: MATCHED, NOT MATCHED, or NOT MATCHED BY SOURCE.
Conditions de fusion
Voyons ce que signifient les différentes conditions:
MATCHED – ce sont des lignes satisfaisant la condition de correspondance. Ils sont communs aux tables source et cible. Dans notre diagramme, ils sont représentés en vert. Lorsque vous utilisez cette condition dans une déclaration de fusion, vous; le plus comme la mise à jour des colonnes de ligne cible avec les valeurs de colonne sourceTable.
NOT MATCHED – Ceci est également connu comme NOT MATCHED BY TARGET; ce sont des lignes de la table source qui ne correspondent à aucune ligne de la table cible. Ces lignes sont représentées par la zone bleue ci-dessus. Dans la plupart des cas, cela peut être utilisé pour déduire que les lignes source doivent être ajoutées à la table cible.
NON MATCHÉ PAR SOURCE – ce sont des lignes de la table cible qui n’ont jamais été mises en correspondance par un enregistrement source; ce sont les lignes dans la zone orange. Si votre objectif est de synchroniser complètement les données targetTable avec la source, vous utiliserez cette condition de correspondance pour SUPPRIMER des lignes.
Si vous ne parvenez pas à comprendre comment cela fonctionne, considérez que la condition de fusion est comme une condition de jointure. Les ROWS dans la section verte représentent les lignes qui correspondent à la condition de fusion, les lignes dans la section bleue sont les lignes trouvées dans le SourceTable, mais pas dans la cible. Les lignes de la section orange sont les lignes trouvées uniquement dans la cible.
Avec ces scénarios de correspondance, vous pouvez facilement intégrer des activités d’ajout, de suppression et de mise à jour dans une seule instruction pour synchroniser les modifications entre deux tables.
Regardons un exemple.
Exemple MERGE
Supposons que notre objectif est de synchroniser toutes les modifications apportées à esqlProductSource avec esqlProductTarget. Voici un diagramme de ces deux tableaux:
Note: Pour les besoins de cet exemple, j’ai exécuté les scripts dont j’ai parlé dans l’introduction pour créer et remplir deux tables: esqlProductSource et esqlProductTarget.
Avant de construire l’instruction MERGE, voyons comment nous synchroniserions la table en utilisant l’instruction UPDATE, INSERT et DELETE pour modifier, ajouter , et supprimez les lignes de la table cible.
Je pense qu’une fois que vous voyez comment nous procédons individuellement, il est plus logique de combiner en une seule opération.
Utiliser UPDATE pour synchroniser les modifications d’une table à l’autre
Pour mettre à jour la table cible avec les valeurs modifiées dans la source du produit, nous pouvons utiliser une instruction UPDATE. Étant donné que le ProductID est la clé primaire des deux tables, il devient notre meilleur choix pour faire correspondre les lignes entre les tables.
Si nous devions mettre à jour les valeurs de colonne dans la table cible en utilisant les colonnes source, nous pourrions le faire en utilisant l’instruction de mise à jour suivante
UPDATE esqlProductTargetSET Name = S.Name, ProductNumber = S.ProductNumber, Color = S.ColorFROM esqlProductTarget T INNER JOIN esqlProductSource S ON S.ProductID = T.ProductID
Cette instruction mettra à jour la colonne dans esqlProductTarget avec les valeurs de colonne correspondantes trouvées dans esqlProductSource pour les identificateurs de produit correspondants.
INSERT Rows Found in one Table but not the Other
Voyons maintenant comment peut identifier les lignes de la table source que nous devons insérer dans la cible de produits. Pour ce faire, nous pouvons utiliser une sous-requête pour trouver des lignes dans la table source qui ne sont pas dans la cible.
INSERT INTO esqlProductTarget (ProductID, Name, ProductNumber, Color)SELECT S.ProductID, S.Name, S.ProductNumber, S.ColorFROM esqlProductSource SWHERE NOT EXISTS (SELECT T.ProductID FROM esqlProductTarget T WHERE T.ProductID = S.ProductID)
Remarque: je pourrais également utiliser une jointure externe Faire la même chose. Si vous souhaitez savoir pourquoi, consultez cet article.
Cette instruction insérera une nouvelle ligne dans esqlProductTarget à partir de toutes les lignes de esqlProductSource qui ne se trouvent pas dans esqlProductTarget.
Suppression Lignes
Cette dernière activité de synchronisation que nous devons faire, elle supprime toutes les lignes de la table cible qui ne sont pas dans SQL Source. Comme nous l’avons fait avec l’instruction insert, nous allons utiliser une sous-requête. Mais cette fois, nous identifierons les lignes de esqlProductTarget introuvables dans esqlProductSource. Voici l’instruction DELETE que nous pouvons utiliser:
DELETE esqlProductTargetFROM esqlProductTarget TWHERE NOT EXISTS (SELECT S.ProductID FROM esqlProductSource S WHERE T.ProductID = S.ProductID)
Maintenant que vous avez vu comment effectuer les différentes opérations individuellement, voyons comment elles se combinent dans le merge.
Notez que la plupart du gros du travail est effectué par la condition de fusion et ses résultats. Plutôt que d’avoir à configurer la correspondance à plusieurs reprises, comme nous l’avons fait dans l’instruction delete, elle est effectuée une fois.
Comparez à nouveau l’instruction Insert à l’instruction merge ci-dessus.
INSERT INTO esqlProductTarget (ProductID, Name, ProductNumber, Color)SELECT S.ProductID, S.Name, S.ProductNumber, S.ColorFROM esqlProductSource SWHERE NOT EXISTS (SELECT T.ProductID FROM esqlProductTarget T WHERE T.ProductID = S.ProductID)
Étant donné que l’instruction MERGE établit la table source et cible, ainsi que leur correspondance, tout ce qui est codé en couleur en rouge est redondant; par conséquent, pas dans la partie insertion de la fusion.
Journalisation des modifications MERGE à l’aide de OUTPUT
Vous pouvez utiliser la clause OUTPUT pour enregistrer les modifications. Dans ce cas, la variable spéciale $ action peut être utilisée pour enregistrer l’action de fusion. Cette variable prendra l’une des trois valeurs suivantes: « INSERT », « UPDATE » ou « DELETE ».
Nous continuerons d’utiliser notre exemple, mais cette fois, nous enregistrerons les modifications et résumerons les
Si ce qui précède est exécuté sur de nouveaux exemples de données, le résumé suivant est généré:
