Je vais aujourd’hui vous faire partager un sujet qui est dans ma to-do list depuis plusieurs mois et dont je me sers très souvent : l’instruction MERGE en SQL.

Cette instruction est apparue avec SQL Server 2008 et permet de combiner plusieurs opérations DML (Data Modification Language -> INSERT, UPDATE, DELETE) en une seule instruction. Elle est très pratique pour synchroniser 2 tables entre elles. Voici les principaux éléments à mettre en place, que je vais appliquer à un exemple :

  • Une source contenant les résultats de référence. Cela peut-être une requête, une vue ou une CTE (Common Table Expression). Dans mon exemple, la source contient des aliments (fruits, légumes, viandes…) et un statut permettant de savoir si celui-ci est disponible à la vente ou non (0 = non ; 1 = oui).
Id Name Category Statut
2 Carotte Légumes 1
3 Pomme Fruits 0
4 Mangue Fruits 1
5 Banane Fruits 0
6 Framboise Fruits 1
7 Fraise Fruits 0
8 Nectarine Fruits 1
9 Courgette Légumes 1
10 Concombre Légumes 0
11 Choix Légumes 0 
WITH source AS ( 
    SELECT 
        [Id], 
        [Name], 
        [Category] 
    FROM 
        [Stock] 
    WHERE
        [Statut] = 1 
)
  • Une table cible, sur laquelle viendront s’appliquer les opérations (INSERT, UPDATE et DELETE).
Id Name Category
1 Ananas Fruits
2 Poivron Légumes 
MERGE INTO [Cart] AS target
  • Une ou plusieurs conditions de jointure qui permettent de définir comment sont rapprochées les lignes entre la source et la cible (équivalent du JOIN). Généralement, on utilise les clés primaires et/ou étrangères des tables concernées (il faut que ce soit un identifiant unique). Attention, cette clause ON n’est pas un filtre, elle est uniquement utilisée pour déterminer la clause WHEN à activer. Si vous devez faire du filtrage, il faut le faire directement au niveau de la source. Dans mon exemple, c’est ce que j’ai fait avec la clause WHERE [Statut] = 1  pour indiquer que je ne souhaite que les aliments disponibles à la vente.
USING source ON source.[Id] = target.[Id]
  • Une ou plusieurs opérations DML en fonction du résultat de la jointure qui peut être de 3 types :

- MATCHED : la ligne correspond exactement aux conditions de jointures. Elles sont donc communes entre la cible et la source. Généralement, cela va déboucher sur une opération d’UPDATE. Il est possible d’avoir 2 clauses WHEN MATCHED, mais dans ce cas, la 1ère doit être accompagnée d’une clause AND et l’une doit produire un UPDATE, l’autre un DELETE. L’instruction MERGE ne peut pas mettre à jour ou supprimer la même ligne plusieurs fois.

WHEN MATCHED AND (
        target.[Name] <> source.[Name] OR target.[Category] <> source.[Category]
    ) 
    THEN UPDATE SET 
        target.[Name] = source.[Name], 
        target.[Category] = source.[Category]

- NOT MATCHED (BY TARGET) : la ligne de la source ne matchent pas avec la cible. Dans ce cas, on va généralement réaliser une opération d’INSERT.

WHEN NOT MATCHED BY TARGET 
    THEN INSERT (
            [Name], 
            [Category]
        ) 
        VALUES (
            source.[Name], 
            source.[Category]
        )

- NOT MATCHED BY SOURCE : la ligne de la cible n’est pas présente dans la source. Si l’objectif est de faire une synchronisation de la source vers la cible, alors on va utiliser une opération de DELETE. Il est possible d’avoir 2 clauses WHEN NOT MATCHED BY SOURCE (même condition que pour le WHEN MATCH).

WHEN NOT MATCHED BY SOURCE 
    THEN DELETE
  • Au moins l’une de ces 3 clauses doit obligatoirement être spécifiée et l’ordre n’importe pas.
  • De manière optionnelle, une clause OUTPUT permet de tracer les actions réalisées par l’instruction MERGE (pour historiser ou logger par exemple)
OUTPUT 
    $ACTION AS Action, 
    ISNULL(INSERTED.[Id], DELETED.[Id]) AS Id, 
    ISNULL(INSERTED.[Name], DELETED.[Name]) AS Name, 
    ISNULL(INSERTED.[Category], DELETED.[Category]) AS Category;

Voilà le résultat suite à l’exécution de l’instruction MERGE :

Action Id Name Category Explication
INSERT 3 Mangue Fruits L’entité était présente dans la source mais pas dans la cible. On réalise donc une insertion.
INSERT 4 Framboise Fruits
INSERT 5 Nectarine Fruits
INSERT 6 Courgette Légumes
DELETE 1 Ananas Fruits L’entité n’était pas présente dans la source, on la supprime.
UPDATE 2 Carotte Légumes Le même ID existait dans la source et dans la cible, mais avec un nom différent (~poivron). On réalise donc une mise à jour.

Voici ce que nous retourne la table “Cart” :

Id Name Category
2 Carotte Légumes
3 Mangue Fruits
4 Framboise Fruits
5 Nectarine Fruits
6 Courgette Légumes

Je vais conclure par les aspects performances. L’instruction MERGE est un peu plus couteuse que son équivalent INSERT/UPDATE, mais le gain en confort, en sécurité et en simplicité vient, pour moi, contrebalancer cela (hors processus sensible ou demandant des performances de 1er ordre). Pour optimiser les performances, faites bien attention à créer correctement vos index, notamment sur les colonnes du JOIN (au niveau de la source ET de la cible). Microsoft documente d’ailleurs assez bien tous ces aspects.