How to merge two identical databases?

[bluize]

Good morning,

I have two identical databases (the only difference is the actual data, the format and structure is identical) that I need to combine into one database. What is the best way to merge these two databases into one?

Your help is greatly appreciated.

Ben

[Stan Mathews]

Re: How to merge two identical databases?

Would seem that you can append the records from the tables in one database to the tables in the other, one at a time.

[aschone]

Re: How to merge two identical databases?

For each table you need to write an append operation. Select the option "Unique, replace existing" or "Unique only" so you do not duplicate records.

http://wiki.alphasoftware.com/Append+Operations

Edit - Stan is fast

[bluize]

Re: How to merge two identical databases?

Thanks everyone, I'll give it a try ...

Ben

[Al Buchholz]

Re: How to merge two identical databases?

Is the data completely different and linking fields ALWAYS unique/different between the 2?

You don't want to get the data intermingled and confused by having something like this..

db1 customer id 1 - Jim's Hardware

db2 customer id 1 - Joe's Foods

If customer id is the linking field, then all of the transactions for each of these 2 customers will appear for both of the customers....

[bluize]

Re: How to merge two identical databases?

Al,

The data is student, class, demographic data from two student databases. One db has Continuing Education students and the other has Workforce Development students. There is a common/unique student ID between the two databases, i.e., a student that has taken Continuing Education classes may have also taken Workforce Development classes. There is a field called Location that for the Workforce Development students is where the class was held, while that same field in the Continuing Education database contains the information as to what entity held the course, i.e. KCE or LCE or SBDC, etc.

[Ray in Capetown]

Re: How to merge two identical databases?

Well Ben - once you explain how you wish to handle the duplicate student records we can advise on the best course of action.
As Al says, adding all, if you then link to student number later you will get apparent duplicate master or child records.

If you want to make a distinction when copying, then you will have to use xbasic loop. There is relatively easy code to copy one record, change some fields and add the record to the second table.

[Al Buchholz]

Re: How to merge two identical databases?

Al,

The data is student, class, demographic data from two student databases. One db has Continuing Education students and the other has Workforce Development students. There is a common/unique student ID between the two databases, i.e., a student that has taken Continuing Education classes may have also taken Workforce Development classes. There is a field called Location that for the Workforce Development students is where the class was held, while that same field in the Continuing Education database contains the information as to what entity held the course, i.e. KCE or LCE or SBDC, etc.

Then instead of an append all you should use an append unique.... on the list tables... and append all on the transaction tables.

[bluize]

Re: How to merge two identical databases?

Thanks Ray. I understand regarding the master and child. One of my tables links student id to classes they have taken. One student can take multiple classes and one class has multiple students. There should not be an instance where the student takes the same class, therefore, the records are unique. Hope this helps.

[Ray in Capetown]

Re: How to merge two identical databases?

Then Al's solution will work for that scenario.
But you will lose the duplicate student information for the field Location
if you keep only one of a duplicated student code ie unique student code.

[Al Buchholz]

Re: How to merge two identical databases?

There should not be an instance where the student takes the same class, therefore, the records are unique.

I usually find data that I don't expect.. What about the student that has to repeat my class?

Experimenting with the data and verifying that theories are correct is paramount.

It's easy to summarize on keys and get counts to see if the data is what is expected... in the summary table browse right click sort descending to see counts in descending order.

[bluize]

Re: How to merge two identical databases?

Al,

These students are in Adult Education, i.e., Business Leadership, Dog Grooming, Horseshoeing, Flower Arranging. These classes are non credit courses, just CEU's.

The majority of our classes are 8, 10, 12, 16, etc. hour classes. When I teach my classes, they really aren't tested for a letter grade, it is either S (completed course), D (dropped). The information I'm capturing is in large part to see what trends we have in classes offered over the years, which ones draw the heaviest enrollment, etc. How far away the students come to take the classes, etc.

Thanks everyone for your wonderful help as always. Ya'll are the best!!!