Merge 2 dbs with identical schemas

redgate_user99

Hi,

I'm trying to find out if the following is possible with Redgate SQL Data Compare or any other Redgate tool.

I have 2 databases with identical table structures but different data, call them database A and Database B.
I want to merge the data from database A into database B but the same PK IDs (int) (and therefore FKs) are in use in both dbs. So as part of the merge I will need to re-allocate many PKs in the records in database A and also update all FKs referencing these updated PKs in order to maintain the referential integrity of the data from database A as it is merged/inserted into database B.

Initial investigation of the trial version of SQL Data Compare I can't see that the above is possible using this tool but I want to be sure before I discount this product as an option. Any help / confirmation much appreciated.

Many Thanks.

sam.blackburn

If the PKs are auto-inserting (e.g. with IDENTITY) then you should be able to do it - the trick is to choose a different comparison key (e.g. a username or some other identifying attribute of your data instead of the id column). You can do this in the Tables & Views tab of the Edit Project dialog.

bill_elmer

Hi, @sam.blackburn, @"redgate_user99", or anyone else who has attempted this approach:
Can the tooling actually accomplish importing data (all inserts in our case) from one database to another (identical schemas) while automatically re-assigning the identity pk values (and associated fk values) of the imported data as it is deployed into the destination database?
If it cannot be accomplished within a single compare/deployment operation, are there any "work-around" approaches that can be used with the tooling to avoid writing custom migration scripts as much as possible? Would be interested to learn from techniques anyone has used to successfully navigate this complexity in a repeatable way. Thanks in advance.