Unanswered: Transferring data from test to live server
I would like to be able to transfer some data from my test database to the live database (identical schemas).
The problem I have is that the data has many related tables using automatically generated id numbers as the keys. If I was just to transfer the data using dts it would break the relationships because new id's would be generated. I can't enable the force keep id option because those id's might already be used on the live server.
How do I transfer the data maintaining the relationships between the tables and not conflicting with existing data on the live server? Do I need to do some sort of merge replication or is this over complicating the issue?
If I understood correctly your problem is how to update(change) foreign keys after inserting new rows to live database.
I guess the simplest way is to create temporary table for saving old keys and new keys for every table.
Below is just draft how it is possible to do.
create table t_target(id int identity,code varchar(10))
create table t_source(id int identity,code varchar(10))
create table t_tmp(id_old int,id_new)
insert t_target(code) values('IN')
insert t_target(code) values('PA')
insert t_target(code) values('WA')