使用merge..output获取source.id和target.id之间的映射 [英] Using merge..output to get mapping between source.id and target.id

问题描述

非常简化，我有两个表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循环或光标一次将一行(RBAR)插入到Target，然后使用scope_identity()填充TargetChild的FK.
2. 将临时列添加到@Target并插入SourceID.然后，您可以加入该列以在TargetChild中获取FK的TargetID.
@Target的
3. SET IDENTITY_INSERT OFF并自己处理分配新值.您会得到一个范围，然后在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.

我真正想做的是使用insert语句的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

这篇关于使用merge..output获取source.id和target.id之间的映射的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！