Utilisation de merge..output pour obtenir le mapping entre source.id et target.id

Question

De manière très simplifiée, j'ai deux tables Source et Cible.

declare @Source table (SourceID int identity(1,2), SourceName varchar(50))
declare @Target table (TargetID int identity(2,2), TargetName varchar(50))

insert into @Source values ('Row 1'), ('Row 2')

Je voudrais déplacer toutes les lignes de @Source a @Target et connaître le TargetID pour chaque SourceID car il y a aussi les tableaux SourceChild y TargetChild qui doit être copié également et je dois ajouter la nouvelle TargetID en TargetChild.TargetID Colonne FK.

Il existe plusieurs solutions à ce problème.

1. Utilisez une boucle while ou des curseurs pour insérer une rangée (RBAR) à la fois dans Target et utilisez la fonction scope_identity() pour remplir le FK de TargetChild .
2. Ajouter une colonne temporaire à @Target et insérer SourceID . Vous pouvez ensuite joindre cette colonne pour récupérer les données suivantes TargetID pour le FK en TargetChild .
3. SET IDENTITY_INSERT OFF para @Target et de gérer vous-même l'attribution de nouvelles valeurs. Vous obtenez une plage que vous pouvez ensuite utiliser dans TargetChild.TargetID .

Je n'aime pas beaucoup l'un d'entre eux. Celui que j'ai utilisé jusqu'à présent est le curseur.

Ce que j'aimerais vraiment faire, c'est utiliser l'option output de l'instruction d'insertion.

insert into @Target(TargetName)
output inserted.TargetID, S.SourceID
select SourceName
from @Source as S

Mais il n'est pas possible

The multi-part identifier "S.SourceID" could not be bound.

Mais c'est possible avec une fusion.

merge @Target as T
using @Source as S
on 0=1
when not matched then
  insert (TargetName) values (SourceName)
output inserted.TargetID, S.SourceID;

Résultat

TargetID    SourceID
----------- -----------
2           1
4           3

Je voudrais savoir si vous l'avez utilisé ? Si vous avez des idées sur la solution ou si vous voyez des problèmes avec elle ? Cela fonctionne bien dans les scénarios simples, mais quelque chose de moche pourrait se produire lorsque le plan de requête devient vraiment compliqué en raison d'une requête source compliquée. Le pire scénario serait que les paires TargetID/SourceID ne correspondent pas.

Voici ce que dit MSDN à propos de la from_table_name de la sortie clause.

Préfixe de colonne qui spécifie une table incluse dans la clause FROM d'une instruction DELETE, UPDATE ou MERGE, utilisée pour spécifier les lignes à mettre à jour ou à supprimer.

Pour une raison quelconque, ils ne disent pas "lignes à insérer, mettre à jour ou supprimer" mais seulement "lignes à mettre à jour ou supprimer".

Toute réflexion est la bienvenue et toute solution totalement différente du problème initial est très appréciée.

Answer 1

À mon avis, c'est une excellente utilisation de MERGE et de la sortie. Je l'ai utilisé dans plusieurs scénarios et je n'ai rencontré aucune anomalie à ce jour. Par exemple, voici une configuration de test qui clone un dossier et tous les fichiers (identité) qu'il contient dans un dossier nouvellement créé (guid).

DECLARE @FolderIndex TABLE (FolderId UNIQUEIDENTIFIER PRIMARY KEY, FolderName varchar(25));
INSERT INTO @FolderIndex 
    (FolderId, FolderName)
    VALUES(newid(), 'OriginalFolder');

DECLARE @FileIndex TABLE (FileId int identity(1,1) PRIMARY KEY, FileName varchar(10));
INSERT INTO @FileIndex 
    (FileName)
    VALUES('test.txt');

DECLARE @FileFolder TABLE (FolderId UNIQUEIDENTIFIER, FileId int, PRIMARY KEY(FolderId, FileId));
INSERT INTO @FileFolder 
    (FolderId, FileId)
    SELECT  FolderId, 
            FileId
    FROM    @FolderIndex
    CROSS JOIN  @FileIndex;  -- just to illustrate

DECLARE @sFolder TABLE (FromFolderId UNIQUEIDENTIFIER, ToFolderId UNIQUEIDENTIFIER);
DECLARE @sFile TABLE (FromFileId int, ToFileId int);

-- copy Folder Structure
MERGE @FolderIndex fi
USING   (   SELECT  1 [Dummy],
                    FolderId, 
                    FolderName
            FROM    @FolderIndex [fi]
            WHERE   FolderName = 'OriginalFolder'
        ) d ON  d.Dummy = 0
WHEN NOT MATCHED 
THEN INSERT 
    (FolderId, FolderName)
    VALUES (newid(), 'copy_'+FolderName)
OUTPUT  d.FolderId,
        INSERTED.FolderId
INTO    @sFolder (FromFolderId, toFolderId);

-- copy File structure
MERGE   @FileIndex fi
USING   (   SELECT  1 [Dummy],
                    fi.FileId, 
                    fi.[FileName]
            FROM    @FileIndex fi
            INNER
            JOIN    @FileFolder fm ON 
                    fi.FileId = fm.FileId
            INNER
            JOIN    @FolderIndex fo ON 
                    fm.FolderId = fo.FolderId
            WHERE   fo.FolderName = 'OriginalFolder'
        ) d ON  d.Dummy = 0
WHEN NOT MATCHED 
THEN INSERT ([FileName])
    VALUES ([FileName])
OUTPUT  d.FileId,
        INSERTED.FileId
INTO    @sFile (FromFileId, toFileId);

-- link new files to Folders
INSERT INTO @FileFolder (FileId, FolderId)
    SELECT  sfi.toFileId, sfo.toFolderId
    FROM    @FileFolder fm
    INNER
    JOIN    @sFile sfi ON  
            fm.FileId = sfi.FromFileId
    INNER
    JOIN    @sFolder sfo ON 
            fm.FolderId = sfo.FromFolderId
-- return    
SELECT  * 
FROM    @FileIndex fi 
JOIN    @FileFolder ff ON  
        fi.FileId = ff.FileId 
JOIN    @FolderIndex fo ON  
        ff.FolderId = fo.FolderId

Answer 2

Je voudrais ajouter un autre exemple à celui de @Nathan, car je l'ai trouvé quelque peu confus.

Le mien utilise des tables réelles pour la plupart, et non des tables temporaires.

Je me suis aussi inspiré de ce site : un autre exemple

-- Copy the FormSectionInstance
DECLARE @FormSectionInstanceTable TABLE(OldFormSectionInstanceId INT, NewFormSectionInstanceId INT)

;MERGE INTO [dbo].[FormSectionInstance]
USING
(
    SELECT
        fsi.FormSectionInstanceId [OldFormSectionInstanceId]
        , @NewFormHeaderId [NewFormHeaderId]
        , fsi.FormSectionId
        , fsi.IsClone
        , @UserId [NewCreatedByUserId]
        , GETDATE() NewCreatedDate
        , @UserId [NewUpdatedByUserId]
        , GETDATE() NewUpdatedDate
    FROM [dbo].[FormSectionInstance] fsi
    WHERE fsi.[FormHeaderId] = @FormHeaderId 
) tblSource ON 1=0 -- use always false condition
WHEN NOT MATCHED
THEN INSERT
( [FormHeaderId], FormSectionId, IsClone, CreatedByUserId, CreatedDate, UpdatedByUserId, UpdatedDate)
VALUES( [NewFormHeaderId], FormSectionId, IsClone, NewCreatedByUserId, NewCreatedDate, NewUpdatedByUserId, NewUpdatedDate)

OUTPUT tblSource.[OldFormSectionInstanceId], INSERTED.FormSectionInstanceId
INTO @FormSectionInstanceTable(OldFormSectionInstanceId, NewFormSectionInstanceId);

-- Copy the FormDetail
INSERT INTO [dbo].[FormDetail]
    (FormHeaderId, FormFieldId, FormSectionInstanceId, IsOther, Value, CreatedByUserId, CreatedDate, UpdatedByUserId, UpdatedDate)
SELECT
    @NewFormHeaderId, FormFieldId, fsit.NewFormSectionInstanceId, IsOther, Value, @UserId, CreatedDate, @UserId, UpdatedDate
FROM [dbo].[FormDetail] fd
INNER JOIN @FormSectionInstanceTable fsit ON fsit.OldFormSectionInstanceId = fd.FormSectionInstanceId
WHERE [FormHeaderId] = @FormHeaderId

Answer 3

Voici une solution qui n'utilise pas MERGE (avec lequel j'ai eu des problèmes à plusieurs reprises et que j'essaie d'éviter si possible). Elle s'appuie sur deux tables de mémoire (vous pouvez utiliser des tables temporaires si vous le souhaitez) avec des colonnes d'IDENTITÉ qui sont mises en correspondance, et surtout, en utilisant ORDER BY lors de l'INSERT, et des conditions WHERE qui correspondent entre les deux INSERTs... la première contient les ID sources et la seconde les ID cibles.

-- Setup...   We have a table that we need to know the old IDs and new IDs after copying.
-- We want to copy all of DocID=1
DECLARE @newDocID int = 99;
DECLARE @tbl table (RuleID int PRIMARY KEY NOT NULL IDENTITY(1, 1), DocID int, Val varchar(100));
INSERT INTO @tbl (DocID, Val) VALUES (1, 'RuleA-2'), (1, 'RuleA-1'), (2, 'RuleB-1'), (2, 'RuleB-2'), (3, 'RuleC-1'), (1, 'RuleA-3')

-- Create a break in IDENTITY values.. just to simulate more realistic data
INSERT INTO @tbl (Val) VALUES ('DeleteMe'), ('DeleteMe');
DELETE FROM @tbl WHERE Val = 'DeleteMe';
INSERT INTO @tbl (DocID, Val) VALUES (6, 'RuleE'), (7, 'RuleF');

SELECT * FROM @tbl t;

-- Declare TWO temp tables each with an IDENTITY - one will hold the RuleID of the items we are copying, other will hold the RuleID that we create
DECLARE @input table (RID int IDENTITY(1, 1), SourceRuleID int NOT NULL, Val varchar(100));
DECLARE @output table (RID int IDENTITY(1,1), TargetRuleID int NOT NULL, Val varchar(100));

-- Capture the IDs of the rows we will be copying by inserting them into the @input table
-- Important - we must specify the sort order - best thing is to use the IDENTITY of the source table (t.RuleID) that we are copying
INSERT INTO @input (SourceRuleID, Val) SELECT t.RuleID, t.Val FROM @tbl t WHERE t.DocID = 1 ORDER BY t.RuleID;

-- Copy the rows, and use the OUTPUT clause to capture the IDs of the inserted rows.
-- Important - we must use the same WHERE and ORDER BY clauses as above
INSERT INTO @tbl (DocID, Val)
OUTPUT Inserted.RuleID, Inserted.Val INTO @output(TargetRuleID, Val)
SELECT @newDocID, t.Val FROM @tbl t 
WHERE t.DocID = 1
ORDER BY t.RuleID;

-- Now @input and @output should have the same # of rows, and the order of both inserts was the same, so the IDENTITY columns (RID) can be matched
-- Use this as the map from old-to-new when you are copying sub-table rows
-- Technically, @input and @output don't even need the 'Val' columns, just RID and RuleID - they were included here to prove that the rules matched
SELECT i.*, o.* FROM @output o
INNER JOIN @input i ON i.RID = o.RID

-- Confirm the matching worked
SELECT * FROM @tbl t