Merge DB

[thickbrain]

I'm sure it is a frequently asked question. I searched the web and cannot find the answer. Most of the hits are abourt mail merge or tools that don't work.

I need to merge several DB that are identical. The problem is they all have primary keys using auto numbering thus same key numbers are found in each DB.

I'm looking for a tool or a script that will help me merge the data and not lose the relationship between records of linked tables.

 

[qdogfball]

Maybe its my brain that is thick.

How can you have several DB that are identical?

Can't you then just use one and delete the others? Why bother merging?

 

[thickbrain]

By identical, I mean they have the same database structure and they are deployed in various location.

They all use auto-numbering for the primary keys.

I need to merge them without loosing the data relationship between tables.

To do so, each key must be renamed and all the related keys in other tables have to reflect the new key value.

I'm not a SQL nor a VB programmer. I know programming using Javascript, Actionscript, Java and few other.

A merge tool or even a VB script would do. Although I'm not a VB guy, I will be able to modify it to suit my needs.

Any leeds?

Thankx.

 

[Simon_MT]

The problem you are going to have is identifying which records relate. You will need to identify how what information "correlates". I had this problem but had real difficulties it was then I introduced VPN and Terminal Servers. I haven't looked back! The TS solution allows the deployment of only one database, one set of documents and images.

Simon

 

[zach]

what if you added a column to source db's like a GUID, and then just merge to the destination db with that, using your guid as a primary key

hope that helps

 

[neileg]

If this is a one off, I would do the following. This works because you can append values into an autonumber PK field, so long as you don't create a key violation.

Identify the numbers of records involved in each database. Let's say you have two databases and the first has 2,000 records with unique PKs and the second has 1,500.
Add a new long integer field to the tables in the second database. Populate this new field by taking the existing autonumber PK value and adding 2,000 to it. Do the same to the FK values. Then take the records from the second database and append them to the first database, making sure that you use the key values with the 2,000 added, and not the original ones. You will then have 3,500 unique PKs and your relationships are preserved.