Merge | SQLServer.fr

Avec SQL Server 2008, la triplette d’instructions INSERT / UPDATE / DELETE s’est vu adjoindre une instruction complémentaire nommée MERGE. Voici une brève présentation de cette nouvelle commande.L’instruction MERGE est présentée dans l’aide en ligne à cette page : https://docs.microsoft.com/fr-fr/sql/t-sql/statements/merge-transact-sql. La syntaxe est un peu hermétique au premier abord, mais cette commande se révèle à l’usage assez puissante et surtout très performante.
En m’appuyant sur certains des exemples présentés dans l’aide en ligne et sur la base de données exemple (voir https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks), je vais détailler quelque peu le premier exemple présent dans l’aide en ligne.

USE AdventureWorks2008R2;
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'

Décomposons la syntaxe MERGE.
Première partie : le mot-clé MERGE lui-même. Il s’agit là de préciser la table cible, c’est-à-dire celle qui sera mise à jour. Dans notre cas, il s’agit de la table Production.ProductionInventory, et l’alias ‘target’ lui est affecté. A noter que ‘target’ n’est ici qu’un simple alias, le fait qu’il soit écrit dans une police bleue n’a rien à voir avec la syntaxe MERGE elle-même, c’est juste une simple coïncidence, ‘target’ est en fait un mot-clé lié à l’instruction CREATE CONTRACT (voir https://docs.microsoft.com/fr-fr/sql/t-sql/statements/create-contract-transact-sql ).
Ensuite, nous avons les données sources, présentées sous forme d’un jeu de résultats (commençant donc par le mot-clé SELECT), introduites par USING. L’alias ‘source’ est ici associé à ce jeu de résultat (là encore, il ne s’agit pas du tout d’un mot clé), avec nommage des colonnes du jeu de résultat.
L’instruction définit ensuite la clause de jointure (mot-clé ON) entre la table cible et le jeu de résultat source. Il est à noter qu’il ne devra pas y avoir d’ambiguïté sur l’action à appliquer à chacune des lignes de la table cible, et que chaque ligne ne peut faire l’objet que d’une action au maximum. Notamment, la clause ON doit être assez précise pour éviter des doublons, et l’instruction MERGE ne peut notamment pas servir à agréger dans la table cible les différentes lignes du jeu de résultat source. Si le besoin consiste à agréger des actions vers une table cible, c’est directement au sein du jeu de résultats source que doivent être agrégées les données (et c’est d’ailleurs le cas ici, puisque la sommation est faite à l’intérieur du SELECT consommé par USING).
Enfin, le plus intéressant, le listing des différentes actions. Et en combinant d’une part les cas de correspondances entre la table cible et le jeu de résultat source, ou ceux des données présentes uniquement d’un des deux côtés (source ou cible), et d’autre part toutes les possibilités de conditions que l’on peut ajouter (en consommant les colonnes de la table cible et / ou du jeu de résultat source), le champ des possibilités devient immense. Dans notre exemple, il y a par exemple une comparaison entre les quantités de la source et de la cible.
Chaque condition WHEN entraîne une action dans la table cible qui peut être un ajout (INSERT), une suppression (DELETE) ou une mise à jour (UPDATE). L’ordre des clauses WHEN a une importance certaine, car dès lors qu’une association source / cible a vérifié l’une des clauses, le traitement de cette association s’arrête là. Ainsi, dans l’exemple, l’action UPDATE de la deuxième clause WHEN n’est exécutée que sur les enregistrements n’ayant pas été traités par la première clause WHEN.
Et la force de l’instruction MERGE réside surtout dans le fait que la table cible n’est parcourue qu’une seule fois. Ainsi, là où, sans cette instruction, il y aurait besoin d’un parcours pour tester les conditions, puis d’un autre pour la première action, et enfin d’une autre pour la deuxième action.

Pour terminer l’instruction (juste avant son point-virgule obligatoire), la clause Output (présente depuis SQL Server 2005), nous donne accès aux tables virtuelles inserted et deleted, complétées d’une colonne $action qui permet de préciser l’action appliquée sur chaque ligne impactée dans la table cible.
N’hésitez pas à me contacter si vous avez des questions par rapport à cette instruction, c’est l’une de mes préférées sous SQL Server 2008.

Laisser un commentaire Annuler la réponse