CODEWITHSUNDEEP
mysqlsql
Afshin Moazami
Afshin Moazami

Reputation: 2098

Merging two databases with the same design (Mysql)

I have two databases with the same design, and different records in Mysql There are two main tables with a unique primary key. (I mean if two records in different databases has the same primary keys they are the same.)

These two tables has some many to many relations to other tables. But the problem is the small tables PK is not unique in all the DBs (auto increment)

Now I want to merge the data of these two databases.

Any Idea?

Upvotes: 1

Views: 1768

Answers (3)

Eric Petroelje
Eric Petroelje

Reputation: 60518

Simplest way would probably be to use a query like this for each table:

INSERT IGNORE INTO db2.sometable
SELECT * FROM db1.sometable

Just make sure you handle the tables in the right order so that you don't run into foreign key problems (e.g. do your two main tables first, then the related tables)

Upvotes: 1

CodeRedick
CodeRedick

Reputation: 7415

EDIT: I think I misread your statement that the keys are unique across databases. In this case I would just not insert a row from the original database if it's already in the target database. You can still copy in the foreign key tables with the same foreign keys then.

so basically:

INSERT INTO MainTable (PK, field1, field2)
SELECT (PK, field1, field2)
FROM OldMainTable
WHERE PK NOT IN (SELECT PK FROM MainTable)

Leaving the old answer below just in case.

Your best bet is to modify the tables to have an OldPrimaryKey field. Then when you insert data into the target database, store the original PK in the OldPrimaryKey field. Then when you insert anything with a foreign key, look match the FK up to the OldPrimaryKey field and update the foreign key to match. (Probably a good idea to create a copy of the foreign key as well just in case!)

SO the queries might look something like this:

INSERT INTO MainTableTarget  (OldPrimaryKey, field1, field2...)
SELECT PrimaryKey as OldPrimaryKey, field1, field2...
FROM OldMainTable

INSERT INTO Table2Target ( OldTable2PK, MainTableForeignKey, fielda, fieldb...)
SELECT Table2PK as OldTable2PK, 
        (SELECT PK FROM MainTableTarget) as MainTableForeignKey,  --This get the FK to point at the newly created database record
        fielda,
        fieldb
FROM OldTable2

It'll probably be a pain, but I don't think there are any non-painful options here...

Oh, and sorry if the syntax is a little off... I'm a T-SQL guy so don't really know MySQL's syntax. Hopefully you get the idea though...

Upvotes: 2

Assaf Karmon
Assaf Karmon

Reputation: 923

This answer depends heavily on your assumption that "if two records in different databases has the same primary keys they are the same." Assuming that your data is clean, set foreign key checks to 0. Then 

INSERT INTO db2.sometable

SELECT * FROM db1.sometable on duplicate key ignore

and not insert ignore which will ignore any error not just duplicates. Do That for each table and then set foreign key checks to 1. If you want to be extra safe you can run a topological sort your tables and insert root tables first while keeping the foreign key checks active. If you have synthetic primary keys, all bets are off and you will need to reassign keys which is a much harder.

Upvotes: 2

Related Questions