sql-server - 使用 merge..output 获取 source.id 和 target.id 之间的映射

Question

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

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 并知道每个 SourceID 的 TargetID > 因为还需要复制表 SourceChild 和 TargetChild，并且我需要将新的 TargetID 添加到 TargetChild.TargetID FK 列。

对此有几种解决方案。

1. 使用 while 循环或游标一次向 Target 插入一行 (RBAR)，并使用 scope_identity() 填充 TargetChild 的 FK。
2. 向 @Target 添加临时列并插入 SourceID。然后，您可以加入该列以获取 TargetChild 中 FK 的 TargetID。
3. @Target 的SET IDENTITY_INSERT OFF 并自行处理分配新值。您将获得一个范围，然后在 TargetChild.TargetID 中使用。

我不太喜欢其中任何一个。到目前为止我使用的是光标。

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

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

但这不可能

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

但是通过合并是可能的。

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

结果

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

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

MSDN 对 output 的 from_table_name 有这样的说法。条款。

Is a column prefix that specifies a table included in the FROM clause of a DELETE, UPDATE, or MERGE statement that is used to specify the rows to update or delete.

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

欢迎任何想法，并且非常感谢对原始问题的完全不同的解决方案。

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