Merging forked database

JPotter

Hello

Please help. It seems Data Compare is the ideal tool for doing what I'm trying to do, but the deployment script isn't resulting in quite the outcome I was expecting. Here is the problem.

I have two databases with identical schema. One was created from the original, except for the customer and other transnational tables. So most of the values in attribute tables and what not were identical at the time of the fork.

Over time, as new values were added to tables, and as the ID fields auto-increment I now have various foreign keys with the same id but different values in both databases. For example

Database X - Table Eye Color

ID Eye_Color
1 Green
2 Blue
3 Brown


And in the Customer table, foreign keys for eyecolor in records refer those ids.

Now in the new database its identical up until a certina point in time

Now ID 4 in Datbase X is Hazel but in Datbase y its Black.

I am now merging the two databases. I need to merge the Lookup tables and give any duplicates unique IDs, then go back to the records with the foreign keys and update changes where the foreign key was changed and given a new ID.


I was hoping Data Compare can do this. Comparing the 2 databases finds all the differences perfecly, however; when I deploy, it doesn't create a new unique database with data from both, I only get data from one database. Hopefully that makes sense.
So my question is can Data Compare generate the script I need? If so, how?

Thanks!
Jason

Andrew P

Hey JPotter,

Thanks for contacting us!
It sounds like you would like to Merge the records between these two tables- as you have noticed, when a comparison is done, it compares based on comparison keys (usually indexes, PKs and FKs) and finds which keys are missing from the Source, missing from the Target, or occurring in both but with different values. (more info on the comparison results here https://documentation.red-gate.com/disp ... on+results)

However you can manually set the comparison keys- so that instead of basing the comparison on indexes or FKs that may match but have different data, you can compare the data- say the comparison key is Eye Color so that it compares based on that rather than something meaningless and wrong (in this instance) like the FK. (see the "Selecting the comparison key" section in here https://documentation.red-gate.com/disp ... +and+views )

Once you are comparing based on data that is not arbitrarily different, you can get a better idea of what you need to merge. You can generate Insert Scripts for a merged table by deploying the Source Only data from each database (so compare once, generate the deployment script for the Source Only data, then switch the Source and the Target and generate the deployment script for the Source Only data).

So this would give you all of the Insert scripts for the merged db- but you still need the schema right?

You would need to create a database with the desired structure- SQL Data Compare only does data, so SQL Compare may help you deploy the objects that you desire or you may need to manually generate/modify the structure- you would then apply the generated Insert scripts to the new db.

This requires some manual stitching but it may be possible depending on how different your new database needs to be.

I will also follow up with you via email in case you have any questions or issues with the above!

Kindly,