在表之间移动数据并生成新旧标识值映射的最佳方法

使用新ID的标识列创建映射表。从源表插入此表,创建映射。

SET IDENTITY_INSERT ON

从连接到映射表的源表插入到目标表中,然后 SET IDENTITY_INSERT OFF .

我创建了一个基于 OUTPUT MERGE 陈述。不 IDENTITY_INSERT 必修的。

RecordImportQueue 和 RecordDataImportQueue ,和 RecordDataImportQueue.RecordID 是一个 RecordImportQueue.RecordID Record 和 RecordData

RecordImportQueue to Record使用 合并 语句,从其 ,并将RecordDataImportQueue转到 记录数据 使用 INSERT 从连接到映射表的源表中选择。

DECLARE @MappingTable table ([NewRecordID] [bigint],[OldRecordID] [bigint])

MERGE [dbo].[Record] AS target
USING (SELECT [InstanceID]
            ,RecordID AS RecordID_Original
            ,[Status]
        FROM [RecordImportQueue]
        ) AS source
ON (target.RecordID = NULL) -- can never match as RecordID is IDENTITY NOT NULL.
WHEN NOT MATCHED THEN
    INSERT ([InstanceID],[Status])
    VALUES (source.[InstanceID],source.[Status])
    OUTPUT inserted.RecordID, source.RecordID_Original INTO @MappingTable;

INSERT INTO [dbo].[RecordData]
    ([InstanceID]
    ,[RecordID]
    ,[Status])
SELECT [InstanceID]
    ,mt.NewRecordID -- the new RecordID from the mappingtable
    ,[Status]
FROM [dbo].[RecordDataImportQueue] AS rdiq
JOIN @MappingTable AS mt
ON rdiq.RecordID = mt.OldRecordID

虽然在最初的帖子之后很久,我希望这能帮助其他人,我很好奇任何反馈。