Synchronize tables with same PK but different data
eltoro
Posts: 2
Hi,
I was wondering if and if, with which version of SQL Data Compare that supports synchronization of a table from two different databases. The thing it needs to manage is to update the PK as well during the synchronization process.
I have two databases with the same table schemas. Lets say that both databases has a table "People". The PK of table "People" is called PeopleID.
In DatabaseA.People, PeopleID=1, Name=ElToro.
In DatabaseB.People, PeopleID=1, Name=SantaClause.
I need the synchronization process to add SantaClause to TableA but with a new PeopleID. They should not be merged into the same PeopleID. The resulting DataBaseA.People should look like
1 | ElToro
2 | SantaClause
To make things worse, we have tables with ForeignKeys to PeopleID which needs to be updated with the new PeopleID at the same time.
Is this possible with SQL Data Compare?
I was wondering if and if, with which version of SQL Data Compare that supports synchronization of a table from two different databases. The thing it needs to manage is to update the PK as well during the synchronization process.
I have two databases with the same table schemas. Lets say that both databases has a table "People". The PK of table "People" is called PeopleID.
In DatabaseA.People, PeopleID=1, Name=ElToro.
In DatabaseB.People, PeopleID=1, Name=SantaClause.
I need the synchronization process to add SantaClause to TableA but with a new PeopleID. They should not be merged into the same PeopleID. The resulting DataBaseA.People should look like
1 | ElToro
2 | SantaClause
To make things worse, we have tables with ForeignKeys to PeopleID which needs to be updated with the new PeopleID at the same time.
Is this possible with SQL Data Compare?
Comments
If your PK on the table can actually utilise more than one column, then you may be able to achieve it. For instance:
PeopleID SiteID Name
1 1 ElToro
1 2 SantaClaus
But this would obviously not help with the related tables; the schema would need amending so they use the composite key as well.
We have a document detailing some of the problems and solutions encountered in trying to synchronise databases, here - but I don't know how easily you'll be able to work with your specific situation
Redgate Software