Synchronize tables with same PK but different data

eltoroeltoro Posts: 2

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?


  • Options
    Unfortunately I don't think this kind of situation is going to be very easy to deal with.

    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
    Systems Software Engineer

    Redgate Software

Sign In or Register to comment.