merge two databases --how?

[nameless_centurian]

The client has an existing database. We have one as well. We aren't needing so much to merge the schemas but rather insert our data into their database. The only thing I can think to do is to write something that pulls it out of ours and inserts it into theirs.

Is there some methodology I could read about that brings a lot of oranization to this process?

 

[maxedoutcc]

what is the dbms you are running? most dbms's have import utilities that connect to variosu datasources, just use their tool and it should be easy.

 

[nameless_centurian]

They are running SQL server 2000. We are running MySQL for this particular database. How do such tools work when the schemas are entirely different?

What we want to do is insert into their database the data we have that they want. Their fields are named differently. Their tables are constructed differently. They enforce different constraints in different places than we do. Their key values that refer to the same data will be different than our key values.

This is why I asked for a methodology. I know the technical bits to get it done; I'm asking for a process to keep it organized and to reduce error.

 

[PTNL]

The methodology of transferring data organized by one unique schema to being stored within another unique schema is usually just a one-way street, and usually falls within either of these options:

A) Smaller databases may require custom export/import functions to be made. Implementations may vary depending on the target/destination database structure: CSV exports, small custom applications, SQL scripts, etc. Expect a lot of manual testing and prior decision making on how errors should be handled ala "garbage in, data out". Have a select group of testers that's familiar with the data to test the final conversions; though expect to have them testing after each iteration of your custom conversion to verify functionality.

B) Larger databases/applications may have an import tool that is written by the vendor of the destination database. These tools usually provide a common front-end (Excel, for example), and have you enter the data to be entered into their system within the various columns they show. The vendor's utility will then handle the reading of each row and inserting data as needed within their schema. (SAP is one example of a vendor that does this). Error handling and decision making is handled by the vendor's utility, but still plan on having a select group of people to poke through the destination app to ensure expected data shows up.


Judging by the OP's comments, I'd say that you'll be doing option A.

 

[nameless_centurian]

A) Expect a lot of manual testing and prior decision making on how errors should be handled ala "garbage in, data out". Have a select group of testers that's familiar with the data to test the final conversions; though expect to have them testing after each iteration of your custom conversion to verify functionality.

Thanks for the comments. These are the specific issues I am asking about. With work needing to be done on both our end and their end, how are these things usually accomplished in an organized manner? The people we will be talking to aren't all that technical and we likely won't have much access to the technical minds on their end beyond the initial meeting.

I'm sorry I keep using the same words repeatedly, but I can't think of a better way to describe it. I would like to learn about a formal way, a process/methodology of accomplishing this task so that we can do the insertions smoothly and also isolate errors when they happen --so we know whose fault it is, ours or theirs...

...kind of like how there are formal processes to allocating tests for subsystems, defining those tests, determining risk of over or under testing, etc. (can you tell I'm a John Musa fan, haha?).

 

[Monkey God]

2000 comes with the ability to make simple DTS packages. Dont remember how hard it is to setup a MySQL connection.
Prolly could use ODBC.

Make out the schema, import it. No big deal. Then just have someone approve the data quality after the fact.
test it of course with a different DB with the same schema.

 

[PTNL]

Thanks for the comments. These are the specific issues I am asking about. With work needing to be done on both our end and their end, how are these things usually accomplished in an organized manner? The people we will be talking to aren't all that technical and we likely won't have much access to the technical minds on their end beyond the initial meeting.

I'm sorry I keep using the same words repeatedly, but I can't think of a better way to describe it. I would like to learn about a formal way, a process/methodology of accomplishing this task so that we can do the insertions smoothly and also isolate errors when they happen --so we know whose fault it is, ours or theirs...

...kind of like how there are formal processes to allocating tests for subsystems, defining those tests, determining risk of over or under testing, etc. (can you tell I'm a John Musa fan, haha?).

You're more hinting at project management, use cases, defect testing, and other processes that are more common with full-blown application development. These high-level concepts are still applicable for what you need to do, but the level of detail needs to be weighed against the complexity of the database schema (likely small enough for one person to fully figure understand the schema) and especially against the fact that this is a one-time deal.

Also given that this is a one-time deal, you may not have enough calendar time to really dive full scale project management methodologies prior to this migration's needed completion date. But this shouldn't be a big issue, as any business rules and data enforcement will have already been built into the database/application; you're just interested in dumping data, not re-inventing the wheel. More of your time, however, will be spent in troubleshooting various aspects of data conversions, how parent/child records are treated, and tinkering with how the application's queries expect data to be received.


Edit: If possible, try to establish a contact with the vendor of the destination database/application/product. Someone that is knowledgeable on how data *should* be in the database, and is able to troubleshoot awkward data conversion stuff when things don't look right within the application. You may want them to sign an NDA so that they are contractually bound to not share or use your data you're trying to migrate. This is useful in case they need a full database backup to reproduce issues and see what's going on from their end.

If necessary, you can try getting help on the database schema from the previous vendor -- just don't expect them to willingly help you migrate off their platform