Here's a tricky one

[Bill Neilsen]

I currently have 2 identical databases running at different company branches.
The data bases have multiple related tables, and because they are at
different branches obviously have different data in them.
The company has now moved into the 21st century and given us the ability to
log into the central server and run the database from all branches.
But, this gives me a problem, the different data.
As I see it, I have 2 ways to go.
1. Have the other branch lose all their data and start again.
2. Somehow, migrate all the data form their multiple tables into the central
database on the server, and conserve all the relationships.
When I think about it, it seems like a pipedream, but, has anyone ever tried
to do this before and succeeded? If it's viewed as possible, then I can then
look for a solution, but if not then I can just get on with it.

 

[DStegon via AccessMonster.com]

Usually far too many PK in the two sets of data to make creating one set that
allows realtionships to still exist. You could write convbersion code that
would APPEND one set of data onto the other and have code change all the
places in the data that the PK are stored for the second, thus keeping stores
2 data together while moving it all into one database. Be very careful doing
this becuase if you renumber a part_id lets say and forget a spot where it is
stored in your data then the possiblity exists that you could be showing that
a customer ordered/bought/etc. a completely different product than they
actually did (assuming sales here but working for whatever the data is
tracking). Can be done... just be very careful!! I would write up a
routine to list all the fields of all the table and sit down and relate where
all the number might change and all the tables that would have to be updated
with the new number. can be done... good luck!!

 

[Fred]

In addition to DS's advice on the DB execution side, you are going to have to
deal with some basic issues. Like, are records duplicated between the two
databases and, if so, are you going to let two copies go into the new
database, or are you going to hunt down and deal with duplicates.

 

[Bill Neilsen]

Thanks for your help guys.
You have convinced me that it's too hard and the danger of introduced errors
would be too great.
I might just keep the other branch's copy for record and history, but start
them anew with the database on the server.
Once again, thanks

 

[Barry A&P]

This is probably completely stupid but i did a similar merge Database A to
Database B by joining tables in my append queries with a different fields.
For example i had a table of transactions in "A" and a table of vendors in
"A" that were joined on VendorID but in database "B" i was compiling my data
to the vendors all had different ID's but same names so i did an select query
in database A with Transactions and Vendors joined on Vendor ID and the
results were appended to Database B with where Vendor Names matched insert
Database B Vendor ID ..

Probably worthless shibboleth
Barry