Insertion, mise à jour et suppression de données à l’aide de MERGE
Dans SQL Server 2008, vous pouvez effectuer des opérations d'insertion, de mise à jour ou de suppression dans une instruction unique en utilisant l'instruction MERGE. L'instruction MERGE vous permet de joindre une source de données à une table ou une vue cible et d'effectuer plusieurs actions sur la cible en fonction des résultats de cette jointure. Par exemple, vous pouvez utiliser l'instruction MERGE pour effectuer les opérations suivantes :
Insérer ou mettre à jour sous condition des lignes dans une table cible.
Si la ligne existe dans la table cible, mettez à jour une ou plusieurs colonnes ; sinon, insérez les données dans une nouvelle ligne.
Synchroniser deux tables.
Insérez, mettez à jour ou supprimez des lignes dans une table cible selon les différences avec les données sources.
La syntaxe MERGE consiste en cinq clauses principales :
La clause MERGE spécifie la table ou vue qui est la cible des opérations d'insertion, de mise à jour ou de suppression.
La clause USING spécifie la source de données qui est jointe avec la cible.
La clause ON spécifie les conditions de jointure qui déterminent le point de correspondance entre la cible et la source.
Les clauses WHEN (WHEN MATCHED, WHEN NOT MATCHED BY TARGET et WHEN NOT MATCHED BY SOURCE) spécifient les actions à effectuer en fonction des résultats de la clause ON et tout critère de recherche supplémentaire spécifié dans les clauses WHEN.
La clause OUTPUT retourne une ligne pour chaque ligne de la cible qui est insérée, mise à jour ou supprimée.
Pour plus de détails sur la syntaxe et les règles, consultez MERGE (Transact-SQL).
Spécification des conditions de recherche sources et cibles
Il est important de comprendre comment les données sources et cibles sont fusionnées en un flux d'entrée unique et comment il est possible d'utiliser des critères de recherche supplémentaires pour filtrer correctement les lignes inutiles. Dans le cas contraire, vous risquez de spécifier des critères de recherche supplémentaires d'une manière qui génère des résultats incorrects.
Les lignes de la source correspondent à des lignes de la cible en fonction du prédicat de jointure spécifié dans la clause ON. Le résultat est un flux d'entrée combiné. Une opération d'insertion, de mise à jour ou de suppression est effectuée pour chaque ligne d'entrée. En fonction des clauses WHEN spécifiées dans l'instruction, la ligne d'entrée peut être l'une des suivantes :
Une paire équilibrée qui se compose d'une ligne de la cible et d'une ligne de la source. Il s'agit du résultat de la clause WHEN MATCHED.
Une ligne de la source qui n'a aucune ligne correspondante dans la cible. Il s'agit du résultat de la clause WHEN NOT MATCHED BY TARGET.
Une ligne de la cible qui n'a aucune ligne correspondante dans la source. Il s'agit du résultat de la clause WHEN NOT MATCHED BY SOURCE.
La combinaison des clauses WHEN spécifiées dans l'instruction MERGE détermine le type de jointure qui est implémenté par le processeur de requêtes et affecte le flux d'entrée résultant. Pour illustrer cela, prenez l'exemple de tables et de données sources et cibles suivant.
USE tempdb;
GO
CREATE TABLE dbo.Target(EmployeeID int, EmployeeName varchar(10),
CONSTRAINT Target_PK PRIMARY KEY(EmployeeID));
CREATE TABLE dbo.Source(EmployeeID int, EmployeeName varchar(10),
CONSTRAINT Source_PK PRIMARY KEY(EmployeeID));
GO
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(100, 'Mary');
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(101, 'Sara');
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(102, 'Stefano');
GO
INSERT dbo.Source(EmployeeID, EmployeeName) Values(103, 'Bob');
INSERT dbo.Source(EmployeeID, EmployeeName) Values(104, 'Steve');
GO
Le tableau suivant répertorie les types de jointure possibles et indique quand chaque type est implémenté par l'optimiseur de requête. Le tableau présente également le flux d'entrée résultant pour les tables sources et cibles en exemple lorsque les critères de recherche pour la mise en correspondance des données sources et cibles sont Source.EmployeeID = Target.EmployeeID.
Type de jointure |
Implémentation |
Résultats du flux d'entrée de l'exemple |
|---|---|---|
INNER JOIN |
La clause WHEN MATCHED est la seule clause WHEN spécifiée. |
SrcEmpID SrcName TrgEmpID TrgName -------- ------- -------- ------- NULL NULL NULL NULL |
LEFT OUTER JOIN |
La clause WHEN NOT MATCHED BY TARGET est spécifiée mais la clause WHEN NOT MATCHED BY SOURCE ne l'est pas. La clause WHEN MATCHED peut ou non être spécifiée. |
SrcEmpID SrcName TrgEmpID TrgName -------- ------- -------- -------100 Mary NULL NULL 101 Sara NULL NULL 102 Stefano NULL NULL |
RIGHT OUTER JOIN |
Les clauses WHEN MATCHED et WHEN NOT MATCHED BY SOURCE sont spécifiées alors que la clause WHEN NOT MATCHED BY TARGET ne l'est pas. |
SrcEmpID SrcName TrgEmpID TrgName -------- ------- -------- -------NULL NULL 103 Bob NULL NULL 104 Steve |
FULL OUTER JOIN |
Les clauses WHEN NOT MATCHED BY TARGET et WHEN NOT MATCHED BY SOURCE sont spécifiées. La clause WHEN MATCHED peut ou non être spécifiée. |
SrcEmpID SrcName TrgEmpID TrgName -------- ------- -------- -------100 Mary NULL NULL 101 Sara NULL NULL 102 Stefano NULL NULL NULL NULL 103 Bob NULL NULL 104 Steve |
ANTI SEMI JOIN |
La clause WHEN NOT MATCHED BY SOURCE est la seule clause WHEN spécifiée. |
TrgEmpID TrgName -------- ------- 100 Mary 101 Sara 102 Stefano |
Les résultats du flux d'entrée de l'exemple indiquent que les résultats de flux d'entrée dépendent de la combinaison de clauses WHEN. Supposons à présent que vous souhaitiez exécuter les actions suivantes sur la table cible en fonction du flux d'entrée :
Insérer des lignes de la table source lorsque l'ID employé n'existe pas dans la table cible et que le nom de l'employé de la source commence par un « S ».
Supprimer des lignes de la table cible lorsque le nom de l'employé de la cible commence par un « S » et que l'ID employé n'existe pas dans la table source.
Pour exécuter ces actions, les clauses WHEN suivantes sont obligatoires :
WHEN NOT MATCHED BY TARGET THEN INSERT
WHEN NOT MATCHED BY SOURCE THEN DELETE
Comme décrit dans le tableau précédent, lorsque les deux clauses WHEN NOT MATCHED sont spécifiées, le flux d'entrée résultant est une jointure externe entière des tables sources et cibles. À présent que les résultats du flux d'entrée sont connus, vous devez déterminer comment les actions d'insertion, de mise à jour et de suppression vont être appliquées au flux d'entrée.
Comme indiqué plus haut, les clauses WHEN spécifient les actions à effectuer en fonction des résultats de la clause ON et de tout critère de recherche supplémentaire spécifié dans les clauses WHEN. Dans de nombreux cas, les conditions de recherche spécifiées dans la clause ON produisent le flux d'entrée requis. Toutefois, dans le scénario fourni en exemple, les actions d'insertion et de suppression requièrent un filtrage supplémentaire pour restreindre les lignes affectées à celles comportant un nom d'employé qui commence par un « S ». Dans l'exemple suivant, des conditions de filtrage sont appliquées aux clauses WHEN NOT MATCHED BY TARGET et WHEN NOT MATCHED BY SOURCE. Le résultat de l'instruction indique que les lignes attendues du flux d'entrée sont corrigées, insérées ou supprimées.
-- MERGE statement with the join conditions specified correctly.
USE tempdb;
GO
BEGIN TRAN;
MERGE Target AS T
USING Source AS S
ON (T.EmployeeID = S.EmployeeID)
WHEN NOT MATCHED BY TARGET AND S.EmployeeName LIKE 'S%'
THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
WHEN MATCHED
THEN UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE AND T.EmployeeName LIKE 'S%'
THEN DELETE
OUTPUT $action, inserted.*, deleted.*;
ROLLBACK TRAN;
GO
Voici les résultats de la clause OUTPUT.
$action EmployeeID EmployeeName EmployeeID EmployeeName
--------- ----------- ------------ ----------- ------------
DELETE NULL NULL 101 Sara
DELETE NULL NULL 102 Stefano
INSERT 104 Steve NULL NULL
(3 row(s) affected)
La réduction du nombre de lignes dans le flux d'entrée en début du processus par l'indication d'une condition de recherche supplémentaire dans la clause ON (par exemple, en spécifiant ON Source.EmployeeID = Target.EmployeeID AND EmployeeName LIKE 'S%') peut semble-t-il améliorer les performances des requêtes. Toutefois, ce faisant, vous risquez d'obtenir des résultats inattendus et incorrects. Étant donné que les conditions de recherche supplémentaires spécifiées dans la clause ON ne sont pas utilisées pour la correspondance des données sources et cibles, elles peuvent être appliquées de manière incorrecte.
L'exemple suivant illustre comment des résultats incorrects peuvent être produits. La condition de recherche pour la correspondance des tables sources et cibles ainsi que la condition de recherche supplémentaire pour le filtrage des lignes sont toutes deux spécifiées dans la clause ON. Dans la mesure où la condition de recherche supplémentaire n'est pas requise pour déterminer la correspondance source et cible, les actions d'insertion et de suppression sont appliquées à toutes les lignes d'entrée. En effet, la condition de filtrage EmployeeName LIKE 'S%' est ignorée. Lorsque l'instruction est exécutée, les résultats des tables inserted et deleted indiquent que deux lignes sont modifiées de manière incorrecte : Mary est supprimée de manière incorrecte de la table cible, et Bob est inséré de manière incorrecte.
-- MERGE statement with join conditions that produce unexpected results.
USE tempdb;
GO
BEGIN TRAN;
MERGE Target AS T
USING Source AS S
ON (T.EmployeeID = S.EmployeeID AND T.EmployeeName LIKE 'S%'
AND S.EmployeeName LIKE 'S%' )
WHEN NOT MATCHED BY TARGET
THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
WHEN MATCHED
THEN UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE
THEN DELETE
OUTPUT $action, Inserted.*, Deleted.*;
ROLLBACK TRAN;
GO
Voici les résultats de la clause OUTPUT.
$action EmployeeID EmployeeName EmployeeID EmployeeName
--------- ----------- ------------ ----------- ------------
DELETE NULL NULL 100 Mary
DELETE NULL NULL 101 Sara
DELETE NULL NULL 102 Stefano
INSERT 103 Bob NULL NULL
INSERT 104 Steve NULL NULL
(5 ligne(s) affectée(s))
Règles de condition de recherche
Les conditions de recherche utilisées pour la correspondance des lignes sources et cibles ainsi que les conditions de recherche supplémentaires utilisées pour filtrer des lignes à partir de la source ou de la cible doivent être spécifiées correctement afin de garantir des résultats corrects. Nous vous recommandons d'observer les règles suivantes :
Spécifiez uniquement des conditions de recherche dans la clause ON <merge_search_condition> qui déterminent les critères pour la correspondance de données dans les tables sources et cibles. 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 ligne dans la clause WHEN appropriée. 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 informations sur la mise à jour de données à l'aide d'une vue, consultez Modification de données par l'intermédiaire d'une vue.
Utilisez la clause WITH <expression de table commune> pour filtrer les lignes des tables sources ou cibles. Cette méthode est semblable à la spécification de critères de recherche supplémentaires dans la clause ON 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.
Exemples
A. Utilisation d'une instruction MERGE simple pour effectuer des opérations INSERT et UPDATE
Supposez que vous avez une table FactBuyingHabits dans une base de données d'entrepôt de données qui garde la trace du dernier achat en date réalisé par chaque client pour un produit spécifique. Une deuxième table, Purchases, située dans une base de données OLTP consigne les achats réalisés pendant une semaine donnée. Chaque semaine, vous souhaitez transférer des lignes correspondants à des produits que des clients spécifiques n'avaient jamais achetés auparavant de la table Purchases vers la table FactBuyingHabits. Pour ce qui concerne les lignes correspondant à des produits que les clients avaient déjà achetés par le passé, il vous faut simplement mettre à jour la date d'achat dans la table FactBuyingHabits. Ces opérations d'insertion et de mise à jour peuvent être effectuées à l'aide d'une instruction unique en utilisant MERGE.
L'exemple suivant crée d'abord les tables Purchases et FactBuyingHabits et y charge quelques exemples de données. La performance des instructions MERGE s'améliore lorsque des index UNIQUE sont créés sur la clé de jointure, afin que des index en clusters soient également créés en créant la contrainte PRIMARY KEY sur la colonne ProductID dans les deux tables.
Dans cet exemple, Purchases contient des achats pour la semaine du 21 août 2006. FactBuyingHabits contient des achats pour la semaine précédente; en temps normal, cette table serait remplie de lignes bien plus anciennes.
USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.Purchases', N'U') IS NOT NULL
DROP TABLE dbo.Purchases;
GO
CREATE TABLE dbo.Purchases (
ProductID int, CustomerID int, PurchaseDate datetime,
CONSTRAINT PK_PurchProdID PRIMARY KEY(ProductID,CustomerID));
GO
INSERT INTO dbo.Purchases VALUES(707, 11794, '20060821'),
(707, 15160, '20060825'),(708, 18529, '20060821'),
(711, 11794, '20060821'),(711, 19585, '20060822'),
(712, 14680, '20060825'),(712, 21524, '20060825'),
(712, 19072, '20060821'),(870, 15160, '20060823'),
(870, 11927, '20060824'),(870, 18749, '20060825');
GO
IF OBJECT_ID (N'dbo.FactBuyingHabits', N'U') IS NOT NULL
DROP TABLE dbo.FactBuyingHabits;
GO
CREATE TABLE dbo.FactBuyingHabits (
ProductID int, CustomerID int, LastPurchaseDate datetime,
CONSTRAINT PK_FactProdID PRIMARY KEY(ProductID,CustomerID));
GO
INSERT INTO dbo.FactBuyingHabits VALUES(707, 11794, '20060814'),
(707, 18178, '20060818'),(864, 14114, '20060818'),
(866, 13350, '20060818'),(866, 20201, '20060815'),
(867, 20201, '20060814'),(869, 19893, '20060815'),
(870, 17151, '20060818'),(870, 15160, '20060817'),
(871, 21717, '20060817'),(871, 21163, '20060815'),
(871, 13350, '20060815'),(873, 23381, '20060815');
GO
Les tables sont maintenant remplies avec les données suivantes :
dbo.Purchases
ProductID CustomerID PurchaseDate
----------- ----------- -----------------------
707 11794 2006-08-20 00:00:00.000
707 15160 2006-08-25 00:00:00.000
708 18529 2006-08-21 00:00:00.000
711 11794 2006-08-20 00:00:00.000
711 19585 2006-08-22 00:00:00.000
712 14680 2006-08-26 00:00:00.000
712 21524 2006-08-26 00:00:00.000
712 19072 2006-08-20 00:00:00.000
870 15160 2006-08-23 00:00:00.000
870 11927 2006-08-24 00:00:00.000
870 18749 2006-08-25 00:00:00.000
dbo.FactBuyingHabits
ProductID CustomerID LastPurchaseDate
----------- ----------- -----------------------
707 11794 2006-08-14 00:00:00.000
707 18178 2006-08-18 00:00:00.000
864 14114 2006-08-18 00:00:00.000
866 13350 2006-08-18 00:00:00.000
866 20201 2006-08-15 00:00:00.000
867 20201 2006-08-14 00:00:00.000
869 19893 2006-08-15 00:00:00.000
870 17151 2006-08-18 00:00:00.000
870 15160 2006-08-17 00:00:00.000
871 21717 2006-08-17 00:00:00.000
871 21163 2006-08-15 00:00:00.000
871 13350 2006-08-15 00:00:00.000
873 23381 2006-08-15 00:00:00.000
Notez qu'il y a deux lignes Client-Produit communes aux deux tables : le client 11794 a acheté le produit 707 pendant la semaine en cours ainsi qu'au cours de la semaine précédente, et c'est également le cas du client 15160 pour le produit 870. Pour ces lignes, nous mettons à jour FactBuyingHabits avec la date enregistrée pour ces achats dans Purchases en utilisant la clause WHEN MATCHED THEN. Nous insérons toutes les autres lignes dans FactBuyingHabits en utilisant la clause WHEN NOT MATCHED THEN.
MERGE dbo.FactBuyingHabits AS Target
USING (SELECT CustomerID, ProductID, PurchaseDate FROM dbo.Purchases) AS Source
ON (Target.ProductID = Source.ProductID AND Target.CustomerID = Source.CustomerID)
WHEN MATCHED THEN
UPDATE SET Target.LastPurchaseDate = Source.PurchaseDate
WHEN NOT MATCHED BY TARGET THEN
INSERT (CustomerID, ProductID, LastPurchaseDate)
VALUES (Source.CustomerID, Source.ProductID, Source.PurchaseDate)
OUTPUT $action, Inserted.*, Deleted.*;
B. Exécution des opérations UPDATE et DELETE
L'exemple suivant utilise la clause MERGE pour mettre quotidiennement à jour la table ProductInventory dans l'exemple de base de données AdventureWorks, selon les commandes traitées dans la table SalesOrderDetail. À l'aide de l'instruction MERGE suivante, la colonne Quantity de la table ProductInventory est mise à jour en soustrayant le nombre de commandes passées chaque jour pour chaque produit. Si le nombre de commandes concernant un produit est tellement élevé 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. Notez que la table source est agrégée sur la ProductID colonne. Sans cela, plusieurs ProductID de la table source pourraient correspondre à la table cible et l'instruction MERGE retournerait une erreur.
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. Exécution des opérations INSERT, UPDATE et DELETE
L'exemple suivant utilise MERGE pour insérer, mettre à jour ou supprimer des lignes dans une table cible selon les différences avec les données sources. Prenons l'exemple d'une petite société qui comporte cinq services, dirigé chacun par un chef de service. La société décide de réorganiser ses services. Pour implémenter les résultats de réorganisation dans la table cible dbo.Departments, l'instruction MERGE doit implémenter les modifications suivantes :
Certains des services en place ne changeront pas.
Certains des services existants seront dirigés par de nouveaux chefs.
De nouveaux services vont être créés.
Certains services vont disparaître après la réorganisation.
Le code suivant crée la table cible dbo.Departments et la remplit avec des gestionnaires.
USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.Departments', N'U') IS NOT NULL
DROP TABLE dbo.Departments;
GO
CREATE TABLE dbo.Departments (DeptID tinyint NOT NULL PRIMARY KEY, DeptName nvarchar(30),
Manager nvarchar(50));
GO
INSERT INTO dbo.Departments
VALUES (1, 'Human Resources', 'Margheim'),(2, 'Sales', 'Byham'),
(3, 'Finance', 'Gill'),(4, 'Purchasing', 'Barber'),
(5, 'Manufacturing', 'Brewer');
Les modifications organisationnelles à apporter aux départements sont stockées dans la table source dbo.Departments_delta. Le code suivant crée et renseigne cette table :
USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.Departments_delta', N'U') IS NOT NULL
DROP TABLE dbo.Departments_delta;
GO
CREATE TABLE dbo.Departments_delta (DeptID tinyint NOT NULL PRIMARY KEY, DeptName nvarchar(30),
Manager nvarchar(50));
GO
INSERT INTO dbo.Departments_delta VALUES
(1, 'Human Resources', 'Margheim'), (2, 'Sales', 'Erickson'),
(3 , 'Accounting', 'Varkey'),(4, 'Purchasing', 'Barber'),
(6, 'Production', 'Jones'), (7, 'Customer Relations', 'Smith');
GO
Enfin, pour que la réorganisation de la société soit reflétée dans la table source, le code suivant utilise l'instruction MERGE afin de comparer la table source, dbo.Departments_delta, à la table cible ,dbo.Departments. La condition de recherche pour cette comparaison est définie dans la clause ON de l'instruction. Selon les résultats de la comparaison, les actions suivantes sont entreprises.
Les services qui existent dans les deux tables sont mis à jour dans la table cible avec de nouveaux noms, de nouveaux chefs de service ou les deux à la fois dans la table Departments. S'il n'y a pas de modification, rien n'est mis à jour. Cette opération est le résultat de la clause WHEN MATCHED THEN.
Tout service présent dans Departments_delta mais qui n'existe pas dans Departments est inséré dans Departments. Cette opération est le résultat de la clause WHEN NOT MATCHED THEN.
Tout service présent dans Departments mais qui n'existe pas dans Departments_delta est supprimé de Departments. Cette opération est le résultat de la clause WHEN NOT MATCHED BY SOURCE THEN.
MERGE dbo.Departments AS d
USING dbo.Departments_delta AS dd
ON (d.DeptID = dd.DeptID)
WHEN MATCHED AND d.Manager <> dd.Manager OR d.DeptName <> dd.DeptName
THEN UPDATE SET d.Manager = dd.Manager, d.DeptName = dd.DeptName
WHEN NOT MATCHED THEN
INSERT (DeptID, DeptName, Manager)
VALUES (dd.DeptID, dd.DeptName, dd.Manager)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
OUTPUT $action,
inserted.DeptID AS SourceDeptID, inserted.DeptName AS SourceDeptName,
inserted.Manager AS SourceManager,
deleted.DeptID AS TargetDeptID, deleted.DeptName AS TargetDeptName,
deleted.Manager AS TargetManager;