Hello. I've recently inherited a system that is a POS (no, not point of sale ). It has two separate app servers that do not share a database server. This means that the applications data resides in two separate databases.

I am in the process of significantly improving the hardware and software infrastructure and apart of that process is migrating the separate databases into one master database and then using replication to provide the high availability.

The problem I am having is I really have no idea how to merge these two databases into one. They have an identical schema but the data is different. The problem is with auto incrementing keys. Since the two databases were started empty, the auto incrementing keys started at 1 so I have primary keys of 1, 2, 3, etc. on both databases.

Does anyone know of a way, tool, etc of merging these two databases and appropriately changing the primary keys when merging so all of the data remains intact. Any help would be greatly appreciated. Thanks.

Are you looking to combine the two original databases (so that every row from the two original databases ends up in the destination database), or merge the two original databases (so that only one set of unique rows ends up in the destination database)? There are a number of important considerations, and I think that a merge is what you really want but I'm not positive of that.

Either way, my first choice would be to replace the autonumbers with guids. This isn't always an option, but it sure makes life simpler! You can then just:

1) copy the working tables into a single table
2) add a guid column to every row
3) if merging, delete the duplicate rows except for the row with the lowest guid of the duplicated rows.
4) use the guid values going forward as the Primary Key.

If changing from autonumber to guid isn't a choice, you can do something very similar using autonumbers, you just have to dance better to make the details work!

-PatP

Thanks for the reply. In answer to your question, I am trying to combine the databases so that every row from both original databases ends up in the destination database.

I am not attached to the auto incrementing numbers in any way so I will have to look into migrating to uuids. Copying the working tables to a single table might be complicated since there are over 40 tables.

Thanks for the insight. This was very helpful.