mai
2012
Le triangle parfait entre FROM, OUTPUT et MERGE
Le but de ce sujet est de vous montrer un intérêt résidant en MERGE et qui est d’offrir une possibilité cruellement absente avant son apparition en SQL Server : à l’insertion, récupérer dans la clause OUTPUT des valeurs provenant de la clause FROM (plutôt que provenant seulement des tables deleted et inserted).
Note :
MERGE est présent en SQL Server depuis la version 2008.
Clause FROM, j’aime :
S’il y a certaines raisons à l’appréciation que je porte à SQL Server (que je connais depuis sa version 2005), l’existence de la clause FROM pour UPDATE (entre autres) est une des plus significatives.
Il n’y a aucun sarcasme tandis que je vous dis que Microsoft a fait, avec cette clause, le choix de s’écarter de la norme SQL avec une perspicacité exemplaire (mais ceci n’est pas le sujet traité). Cette clause qui est aussi présente pour les DELETE est malheureusement absente des opérations INSERT.
À priori, cette absence ne semble pas porter à grande conséquence puisqu’il reste néanmoins possible de faire des insertions sur base de résultats obtenus par select. (insert into … select … from …). Mais cela serait oublier un peu vite la clause OUTPUT qui, si elle n’en continue pas moins d’exister pour les opérations d’insertion, n’a alors pas accès à autre chose qu’aux table inserted et deleted.
INSERT FROM, y a OUTPUT qui te cherche :
Ainsi donc, il n’existe pas de clause FROM à l’opération INSERT.
Alors imaginons un scénario où cela va nous manquer :
Nous avons deux tables temporaires, l’une faisant référence à l’autre (il y a donc emploie de clés) et nous souhaitons copier le contenu de ces deux tables temporaires vers des tables de même structures mais dont les identifiants devront être adaptés (parce qu’il s’agit d’identifiant numériques auto incrémentés par exemple).
Nous allons donc devoir commencer par faire la copie des données de la table « parente/influente » (celle qui est référée par l’autre), récupérer les identifiants des lignes insérées dans la table de destination et ensuite faire la copie des données de la table « fille/dépendante » en exploitant les identifiants précédemment récupérés.
Facile à dire et bien que faisable, le faire est une belle paire de manche (et autant vous dire, qu’en tout cas de figure, il y aura des performances tristement gâchées).
Recréer les relations n’est pas une mince affaire.
MERGE à la rescousse :
MERGE qui a une syntaxe particulière offre non seulement une approche compacte à la fusion de données (insérer ce qui n’existe pas et mettre à jour ce qui existe) mais offre surtout un remède aux problèmes qui ont été mentionnés plus haut. En effet, MERGE permet dans sa clause OUTPUT de récupérer des données provenant tant des tables inserted et deleted que de la table source (table au sens large, un ensemble de résultats).
Ainsi, si l‘on reprend le scénario qui nous inquiétait en considérant une approche avec INSERT, il devient facile pendant l’insertion (avec MERGE) de ligne provenant de la table temporaire « parente/influente » d’en récupérer simultanément l’identifiant d’origine et l’identifiant obtenu après insertion. Cela peut donc permettre d’établir rapidement la correspondance d’un identifiant vers l’autre, ce qui permettra donc une « traduction » des identifiants lors de l’insertion des lignes de la seconde table « fille/dépendante ».
astuce pour avoir un index sur externalID */ , ID INT IDENTITY , UNIQUE (externalID, ID) ) MERGE INTO dbo.Vert AS Dst USING ( SELECT ID , colonne1 , colonne2 FROM #tmpVert ) AS Src ON ( 1 = 2 -- 100% nouvelles lignes ) WHEN NOT MATCHED THEN INSERT (colonne1, colonne2) VALUES (Src.colonne1, Src.colonne2) OUTPUT Src.ID, inserted.ID INTO @Mapping(externalID, internalID) ; -- Le point virgule fait partie de la syntaxe INSERT INTO dbo.Jaune ( vertID , colonne1 ) SELECT M.internalID , Src.colonne1 FROM #tmpJaune AS Src INNER JOIN @Mapping AS M ON ( M.externalID = Src.vertID )DECLARE @Mapping TABLE (
externalID INT
, internalID INT
/* Ci dessous, une
Conclusion :
Le simple fait d’offrir une clause OUTPUT dans laquelle les données de la table source peuvent être récupérées, dote l’opération MERGE d’un avantage pouvant s’avérer essentielle face à l’opération INSERT.
2 Commentaires + Ajouter un commentaire
Commentaires récents
- Et si on se passait des clés étrangères ? dans
- Quand les fonctions tables surpassent les vues et CTE. dans
- Quelques choses à savoir sur les espaces en fin de chaîne dans
- Assigner des variables et renvoyer un résultat en une seule opération dans
- Quelques choses à savoir sur les espaces en fin de chaîne dans
C’était peut-être trop abstrait, tu as raison.
J’ai rajouté du code (SQL Server) qui illustre la résolution du problème avec MERGE et OUTPUT.
Merci pour la suggestion.
Salut
La syntaxe de MERGE et l’application à ton cas des deux tables temporaires ajouteraient une touche spéciale.
@+