views:

159

answers:

3

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.

+4  A: 

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

John Sansom
+1  A: 

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.

ChrisW
Thanks, but this is legacy data - I can't change the schema.
+2  A: 

@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

roman m