Лучший способ перемещать данные между таблицами и генерировать сопоставление старых и новых значений идентификаторов.

Создайте таблицу сопоставления со столбцом IDENTITY для нового идентификатора. Вставьте из исходных таблиц в эту таблицу, создав сопоставление.

SET IDENTITY_INSERT ON для вашей целевой таблицы.

Вставьте в целевую таблицу исходные таблицы, присоединенные к таблице сопоставления, затем SET IDENTITY_INSERT OFF.

Я создал таблицу сопоставления на основе предложения OUTPUT оператора MERGE. IDENTITY_INSERT не требуется.

В приведенном ниже примере есть RecordImportQueue и RecordDataImportQueue, а RecordDataImportQueue.RecordID — это FK для RecordImportQueue.RecordID. Данные в этих промежуточных таблицах должны быть перемещены в Record и RecordData, а FK должен быть сохранен.

RecordImportQueue to Record выполняется с помощью оператора MERGE, создавая таблицу сопоставления из своего OUTPUT, а RecordDataImportQueue переходит к RecordData с помощью INSERT из SELECT исходной таблицы, присоединенной к таблице сопоставления.

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

Хотя спустя долгое время после оригинального сообщения, я надеюсь, что это может помочь другим людям, и мне любопытны любые отзывы.

Я думаю, что временно добавлю дополнительный столбец в новую таблицу для хранения старого идентификатора. После завершения вставки вы можете извлечь сопоставление в другую таблицу и удалить столбец.