Synchronize tables with same PK but different data
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?