How to copy parent and child data from one database to another while assigning the newly created parent ids in the foreign key column of child tables?

Suppose,

I have a table tblClasses and tblStudents

Now each class have multiple student.

tblClass

ID Name
1  ClassA
2  ClassB
3  ClassC

tblStudents

ID  Name  Class_ID
1.  john   1
2.  Mathew 1
3.  Imran  2
4.  Jenny  3

now, I have another server having exact same db and tables and I am copying data from server 1 to server 2 from same tables using the Select and Insert e.g.

insert into server2.dbo.tblClass (Name)
select Name from server1.dbo.tblClass

and for tblStudents

insert into server2.dbo.tblStudents (Name, Class_ID)
select Name, Class_ID from server1.dbo.tblStudents 

now this is ok but the real problem is that in server2 after copying the data, how to populate the tblStudents fk Class_ID with the actual IDs of tblClass which are generated after inserting the data into tblStudents in server2 since PKs are Identity and autoincremented and cannot change the design.

What to do in this case?

In simple words, when a parent and child data are copied then in the child table the foreign key field needs to be populated with the actual IDs of the parent not the one from where it is copied which would be obviously different.

I am not allowed to change the table design or properties and have to do it using the queries.

Any suggestions?

Answer

The way is to create a ClassId mapping table on class records insertion and use this mapping table to translate OldClassId to NewClassId for the new Student table:

declare @ClassIds table (OldClassId int, NewClassId int);


merge into newDB.dbo.tblClasses as target
    using
    (
        select
            Id = Id * (-1),
            [Name]  
        from
            oldDB.dbo.tblClasses
    )
    as source on source.Id = target.Id

when not matched by target then
    insert ([Name])
    values (source.[Name])

output source.Id * (-1), inserted.Id      -- ← the trick is here
into @ClassIds (OldClassId, NewClassId); 


insert into newDB.dbo.tblStudents
select
    s.Id,
    s.[Name],
    ClassId = ids.NewClassId
from
    oldDB.dbo.tblStudents s
    inner join @ClassIds ids on ids.OldClassId = s.ClassId;

The major trick is that the MERGE statement may work with not not only inserted and deleted columns (as INSERT, UPDATE, DELETE statements do) but with the source columns as well.