Migrating a header-detail pair of tables on sql server

Question

How would you migrate SOME records from a header-detail pair of tables (with IDENTITY ID's) to a new pair of tables in another DB?

For example, yo need to migrate record numbered 4,6,9 and 10 from the header table and all their details.

When you INSERT the header records on the new table, their ID's will be 1,2,3 and 4. When inserting the details, the child record from 4 will need to point to 1, 6 to 2 and so on.

Is there a way to use the OUTPUT clause to include a field that is not inserted in the table, as a way to pair "old" ID' to "new" ones?

Answer 1

The OUTPUT clause of an INSERT statement will only allow you to select values that are actually inserted into the target table. You can work around this by using a MERGE instead, as the OUTPUT clause of a MERGE statement will allow you to select values from the source data as well. Here's an example:

declare @src table (id bigint, data char);
declare @dest table (id bigint identity(1,1), data char);
insert @src values (4, 'A'), (6, 'B'), (9, 'C'), (10, 'D');

insert @dest (data)
    output inserted.id, S.id -- ERROR
    select data from @src S;

merge @dest D
    using @src S on 1 = 0
    when not matched then
        insert (data) values (S.data)
        output inserted.id as [New ID], S.id as [Old ID]; -- LEGAL

Results of the MERGE statement:

New ID  Old ID
1       4
2       6
3       9
4       10