使用Merge.输出来获得Sourcee.id和Target t.id之间的映射

Question

使用Merge.输出来获得Sourcee.id和Target t.id之间的映射

非常简单，我有两个表来源和目标。

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')

我想把所有的行从@Source到@Target并且知道TargetID每人SourceID因为还有表SourceChild和TargetChild这也需要复制，我需要添加新的TargetID进TargetChild.TargetIDFK柱

对此有几个解决方案。

1. 使用while循环或游标一次插入一行(Rbar)并使用

   scope_identity()

   来填补.的FK.

   TargetChild.

2. 向

   @Target

   和插入

   SourceID

   ..然后，您可以加入该列，以获取

   TargetID

   为FK在

   TargetChild.

3. SET IDENTITY_INSERT OFF

   为

   @Target

   自己来分配新的价值观。你得到一个范围，然后你用在

   TargetChild.TargetID.

我一点也不喜欢他们。到目前为止我用的是游标。

我真正想做的是使用output插入语句的子句。

insert into @Target(TargetName)output inserted.TargetID, S.SourceIDselect SourceNamefrom @Source as S

但这是不可能的

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

但合并是可能的。

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

结果

TargetID    SourceID----------- -----------2           14           3

我想知道你有没有用过这个？如果你对解决方案有任何想法，或者看到有什么问题吗？它在简单的场景中运行良好，但是当查询计划由于复杂的源查询而变得非常复杂时，可能会发生一些丑陋的事情。最糟糕的情况是TargetID/SourceID对实际上不匹配。

MSDN有关于from_table_name.的.输出量条款。

列前缀，它指定DELETE、UPDATE或Merge语句的FROM子句中包含的表，用于指定要更新或删除的行。

出于某种原因，他们没有说“要插入、更新或删除的行”，而是要更新或删除的行。

任何想法都是受欢迎的，完全不同的解决方案是非常感谢的。

Answer 1

在我看来，这是对合并和输出的一种很好的使用。我在几个场景中使用过，到目前为止还没有经历过任何奇怪的事情。例如，下面是将文件夹和其中所有文件(标识)克隆到新创建的文件夹(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 illustrateDECLARE @sFolder TABLE              (FromFolderId UNIQUEIDENTIFIER, ToFolderId UNIQUEIDENTIFIER);DECLARE @sFile TABLE (FromFileId int, ToFileId int);              -- copy Folder StructureMERGE @FolderIndex fiUSING   (   SELECT  1 [Dummy],                     FolderId,                      FolderName            FROM    @FolderIndex [fi]             WHERE   FolderName = 'OriginalFolder'         ) d ON  d.Dummy = 0WHEN NOT MATCHED THEN INSERT      (FolderId, FolderName)     VALUES (newid(), 'copy_'+FolderName)OUTPUT  d.FolderId,         INSERTED.FolderIdINTO    @sFolder (FromFolderId, toFolderId);-- copy File structureMERGE   @FileIndex fiUSING            (   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 = 0WHEN NOT MATCHED THEN INSERT ([FileName])     VALUES ([FileName])OUTPUT  d.FileId,         INSERTED.FileIdINTO    @sFile (FromFileId, toFileId);-- link new files to FoldersINSERT 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