[SQL Server] T-SQL : les joies de l’instruction MERGE, ou comment effectuer des opérations mixtes en un temps, deux mouvements

Ce billet aborde l’utilisation de l’instruction MERGE en T-SQL.

Présentation de MERGE

L’instruction MERGE est une fonctionnalité T-SQL apparue à partir de SQL Server 2008, et permettant d’effectuer des opérations DML (Data Modification Language) multiples.

Son utilité est telle qu’avant SQL Server 2008, la solution pour combiner diverses opérations DML était de les écrire, chacune, dans une instruction T-SQL séparée, ce qui peut s’avérer vite pénible quand une grande quantité de données doit être traitée suivant des conditions bien spécifiques (i.e., condition d’existence ou non d’un ou plusieurs enregistrements). Et surtout, MERGE permet de ne pas avoir à parcourir une table cible en plusieurs fois.

Voici la syntaxe de création d’une instruction MERGE :

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> ]
[ 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> ]
[ <output_clause> ]
[ OPTION ( <query_hint> [ ,...n ] ) ]

 

L’instruction MERGE est très simple à écrire. Il suffit typiquement de de spécifier une source (dans notre exemple syntaxique : table_source)
contenant l’ensemble de résultats de référence à utiliser et une table cible (target_table) qui est celle qui sera mise-à-jour, ainsi que leur jointure (merge_search_condition) et
le type d’opération DML à réaliser quand les enregistrements entre la source et la cible correspondent (WHEN MATCHED) ou non (WHEN NOT MATCHED).

Exemple d’utilisation de MERGE

Supposons que l’on veuille synchroniser des enregistrements – exemple classique, mais omniprésent dans le monde réel – d’une table appelée GlobalEmployees (employés d’un groupe) dans une table LocalEmployee (employés d’une filiale locale), selon les 3 conditions suivantes :

  1. Si un employé de la table LocalEmployee n’est pas présent dans la table GlobalEmployee, alors il est inséré dans cette dernière.
  2. Si un employé n’a pas le même nom ou âge dans les deux tables, alors la table GlobalEmployee est mise à jour pour correspondre à LocalEmployee, en fonction de l’identifiant.
  3. Si un employé de la table GlobalEmployee n’est pas présent dans LocalEmployee, alors il est supprimé.

Entrons dans le vif du sujet :

  • Créons nos deux tables et remplissons-les :
    • LocalEmployee :
CREATE TABLE LocalEmployee(EmployeeID INTEGER, EmployeeName VARCHAR(20), Age INT)
GO
INSERT INTO LocalEmployee
 SELECT 1,'Durand',44
UNION ALL
 SELECT 2,'Bourdois',22
UNION ALL
 SELECT 3,'Flamant',37
UNION ALL
 SELECT 4,'Ferrand',56
GO
    • GlobalEmployee :
CREATE TABLE GlobalEmployee(EmployeeID INTEGER, EmployeeName VARCHAR(20), Age INT)
GO
INSERT INTO GlobalEmployee
 SELECT 1,'Durand',44
UNION ALL
 SELECT 2,'Bourdoisy',21
UNION ALL
 SELECT 7, 'Wurz', 32
GO
  • Consultons le contenu des deux tables (via un simple SELECT) :
    • LocalEmployee :


    • GlobalEmployee :


  • Appliquons l’instruction MERGE permettant de répondre aux conditions exposées plus haut :
MERGE GlobalEmployee AS target_table
USING LocalEmployee AS source_table
 ON target_table.EmployeeID = source_table.EmployeeID
WHEN NOT MATCHED BY TARGET THEN -- condition 1
 INSERT (EmployeeID, EmployeeName, Age)
 VALUES (source_table.EmployeeId, source_table.EmployeeName, source_table.Age)
WHEN MATCHED THEN -- condition 2
 UPDATE SET target_table.EmployeeName = source_table.EmployeeName, target_table.Age = source_table.Age
WHEN NOT MATCHED BY SOURCE THEN -- condition 3
 DELETE;
GO
  • Apprécions le résultat :
    • Pour LocalEmployee :


    • Pour GlobalEmployee :


Comme on peut le voir, via une simple instruction MERGE combinant les ordres DML traditionnels (INSERT, UPDATE, DELETE), la synchronisation de la table GlobalEmployee avec LocalEmployee dans le respect des 3 conditions énumérées plus haut a été faite avec succès :

  1. Les employés d’identifiant 3 et 4 non-présents dans la table GlobalEmployee ont été insérés dans cette dernière.
  2. L’employé d’identifiant 2 a été mis à jour dans la table GlobalEmployee car son nom ou son âge (dans notre cas, les 2) ne correspondait pas à celui de LocalEmployee.
  3. L’employé d’identifiant 7 de la table GlobalEmployee a été supprimé, car non-présent dans la table LocalEmployee.
Notez que le point-virgule en fin d’instruction MERGE est obligatoire pour éviter toute erreur de parsing. En cas d’omission, vous obtiendrez ce délicieux message d’erreur :

Et les performances dans tout ça ?

Au-delà du fait que l’instruction MERGE permet de réduire le coût de développement de requêtes complexes chargées de multiplier des opérations DML différentes sur les mêmes tables, son autre avantage – et pas des moindres – est sa capacité à centraliser chaque action en une opération qui ne balayera la table cible qu’en une seule fois, même s’il doit tester chaque condition de fusion.

Puisqu’une image vaut mille mots, voici le plan d’exécution de notre requête MERGE :


Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s