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

2021-12-02 00:00:00 merge sql-server-2008 sql-server

非常简单，我有两个表 Source 和 Target.

Very simplified, I have two tables Source and Target.

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

I would like to move all rows from @Source to @Target and know the TargetID for each SourceID because there are also the tables SourceChild and TargetChild that needs to be copied as well and I need to add the new TargetID into TargetChild.TargetID FK column.

对此有几种解决方案.

  1. 使用 while 循环或游标一次向 Target 插入一行 (RBAR)，并使用 scope_identity() 填充 TargetChild 的 FK.
  2. 将临时列添加到 @Target 并插入 SourceID.然后，您可以加入该列以获取 TargetChild 中 FK 的 TargetID.
  3. SET IDENTITY_INSERT OFF 用于 @Target 并自己处理分配新值.您将获得一个范围，然后在 TargetChild.TargetID 中使用该范围.
  1. Use a while loop or cursors to insert one row (RBAR) to Target at a time and use scope_identity() to fill the FK of TargetChild.
  2. Add a temp column to @Target and insert SourceID. You can then join that column to fetch the TargetID for the FK in TargetChild.
  3. SET IDENTITY_INSERT OFF for @Target and handle assigning new values yourself. You get a range that you then use in TargetChild.TargetID.

我不是很喜欢他们中的任何一个.到目前为止，我使用的是游标.

I'm not all that fond of any of them. The one I used so far is cursors.

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

What I would really like to do is to use the output clause of the insert statement.

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 对实际上不匹配.

I want to know if you have used this? If you have any thoughts about the solution or see any problems with it? It works fine in simple scenarios but perhaps something ugly could happen when the query plan get really complicated due to a complicated source query. Worst scenario would be that the TargetID/SourceID pairs actually isn't a match.

MSDN 对 输出from_table_name 有此说明> 子句.

MSDN has this to say about the from_table_name of the output clause.

是列前缀，用于指定包含在 DELETE、UPDATE 或 MERGE 语句的 FROM 子句中的表，用于指定要更新或删除的行.

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.

出于某种原因，他们不说要插入、更新或删除的行"，而只说要更新或删除的行".

For some reason they don't say "rows to insert, update or delete" only "rows to update or delete".

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

Any thoughts are welcome and totally different solutions to the original problem is much appreciated.

推荐答案

在我看来，这是 MERGE 和输出的一个很好的用途.我已经在几个场景中使用过，到目前为止还没有遇到任何奇怪的情况.例如，这里是将文件夹及其中的所有文件(身份)克隆到新创建的文件夹 (guid) 中的测试设置.

In my opinion this is a great use of MERGE and output. I've used in several scenarios and haven't experienced any oddities to date. For example, here is test setup that clones a Folder and all Files (identity) within it into a newly created Folder (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

相关文章