Importing data and keeping ids and relationships

Question

I am working on cleaning up a mess that another programmer started. The created 2 identical databases for different locations but that obviously caused major issues. They are using cakePHP and there are quite a few relationships. I am pretty sure I will have to write a script to import that data from on DB to the other and keep all the relationships but was wondering if there is an easier way to do it.

Answer 1

Which database are you using? How big are the databases in terms of records?

Idea #1

What about a tool like RedGate's SQL Data Compare? It compares the data between two databases and lets you synchronize them.

I used to use this tool all the time to synchronize changes between different production db's and it works like a dream. RedGate's sql tools are top notch (not affiliated with them at all).

SQL Data Compare: http://www.red-gate.com/products/sql_data_compare/index.htm

Idea #2

You'll have to disable foreign key constraints to import all the data if there are lots of relationships. Once you do that you can import the data one table at a time. If you are using a database like mysql, use mysqldump to create the sql inserts. The process of creating a script is mostly automated for you. Still grunt work though.