Copy data and keeping referencial integrity for new IDs

Question

I need a tool or method that allows the transfer of data and automatically updates the foreign keys at the destination table.

The SET IDENTITY_INSERT ON/OFF is not what I'm looking for.

Example:

table master (id int identity, name char)
table slave (id int identity, master_id int, name char)

I would like to create a script like this:

insert into master(name) values ('master a')
insert into master(name) values ('master b')

insert into slave(master_id,name) values ( ?, 'slave aa')
insert into slave(master_id,name) values ( ?, 'slave bb')
insert into slave(master_id,name) values ( ?, 'slave cc')
insert into slave(master_id,name) values ( ?, 'slave dd')

When inserting the slaves, I would like to know what is the new value of the master_id, in order to keep the same relation as in the original table.

Answer 1

Hi,

If you are using Identity columns within your tables, the function SCOPE_IDENTITY() will return the ID of the last inserted record. You can then use this as the foreign key for the secondary table.

http://msdn.microsoft.com/en-us/library/ms190315.aspx

Make sense?

Cheers, John

Answer 2

If you use UniqueIdentifier (Guid) instead of int, then you don't need to change the ID values when you move them from one database to another.

Answer 3

@John Sansom: right on the money

@pv2008:

create proc InsertAndUpdate
@parentName varchar(255),
@childName varchar(255)

as
declare @newParentId int
insert into PARENT values (@parentName)
select @newParentId = SCOPE_IDENTITY()

insert into CHILD values (@newParentId, @childName)

call this sproc every time you insert