sql server - data migration old DB to new DB -- triggers? output clause? what? -

sql server - data migration old DB to new DB -- triggers? output clause? what? -

i migrate info parent , kid tables in old db similar tables in new db. old , new schemas different.

in old db parent entry has (id, ...) , kid entries each have (id, pid, ...), pid id of corresponding parent row.

the question is, how them connected in destination db? i'm stuck. had thought way create table maps oldid newid each element; however, cannot figure out how either triggers, output clause or else i've looked at.

there other tables i'd migrate well, oldid newid mapping table handy. unless there's improve solution.

to explain further... there foreign key relationships in old db need preserved in new db. when row gets copied new db gets new pk. rows in kid tables have fk old pk of parent row. in order re-create kid rows old db new 1 have select them old db parent's old pk insert them new db parent's new pk. that's part can't figure out how sql.

thanks, eric

here's approach:

simply add together idmap column target database table, insert related tables based on bring together idmap.

e.g. tables

create table db1.dbo.parent ( id int identity, description varchar(max), primary key(id) ) create table db1.dbo.child ( id int identity, parentid int not null, somechar char(1), primary key(id), foreign key(parentid) references db1.dbo.parent(id) ) create table db2.dbo.parent ( id int identity, description varchar(max), primary key(id) ) create table db2.dbo.child ( id int identity, parentid int not null, somechar char(1), primary key(id), foreign key(parentid) references db2.dbo.parent(id) )

your import script be:

alter table db2.dbo.parent add together idmap int --might consider adding index performance, might help, might hurt. go insert db2.dbo.parent (description, idmap) select description, id db1.dbo.parent insert db2.dbo.child (parentid, somechar) select db2.dbo.parent.id, db1.dbo.child.somechar db1.dbo.child inner bring together db2.dbo.parent on db2.dbo.parent.idmap = db1.dbo.child.parentid

here's sql fiddle demonstrating above (not strictly speaking cross database because of sql fiddle limitations, should give idea.)

you can drop idmap column, or maintain if want around posterity.

sql-server