Importing MySQL records with duplicate keys

I have two MySQL databases with identical table structure, each populated wit开发者_高级运维h several thousand records. I need to merge the two into a single database but I can't import one into the other because of duplicate IDs. It's a relational database with many linked tables (fields point to other table record IDs).

Edit: The goal is to have data from both databases in one final database, without overwriting data, and updating foreign keys to match with new record IDs.

I'm not sure how to go about merging the two databases. I could write a script I suppose, but there's many tables and it would take a while to do. I wondered if anyone else had encountered this problem, and the best way to go about it?

Just ignore the duplicates. The first time the key is inserted, it'll be inserted. The second time it will be ignored.

INSERT IGNORE INTO myTable (SELECT * FROM myOtherTable );

See the full INSERT syntax here.

The trick was to increment the IDs in one database by 1000 (or something won't overlap data in the target database), then import it.

Thanks for everyone's answers.

Are the duplicate IDs supposed to correspond to each other? You could create a new table with an auto increment field and save the existing keys as two columns.

That would just be a 'bulk copy' though. If there is some underlying relationship then that would dictate how to combine the data.

If you have two tables A1 and A2 and you want to merge this to AA you can do this:

INSERT INTO aa SELECT * FROM A1;
INSERT INTO aa SELECT * FROM A2 ON DUPLICATE KEY 
  UPDATE aa.nonkeyfield1 = a1.nonkeyfield1,
         aa.nonkeyfield2 = a1.nonkeyfield2, ....;

This will overwrite fields with duplicate keys with A2 data.

A slightly slower method with simpler syntax is:

INSERT INTO aa SELECT * FROM A1;
REPLACE INTO aa SELECT * FROM A2;

This will do the same thing, but will not update duplicate rows, but instead delete the row from A1 first and then reinsert the data from A2.

If you want to merge a whole database with foreign keys, this will not work, because it will break the links between tables.

If you have a whole database and you do not want to overwrite data
I'd import the first database as normal into database A.
import the second database into a database B.
Set all foreign keys as on update cascade. Double check this.

Now run the following statement on all tables on database B.

 SELECT @increment:= MAX(pk) FROM A.table1;
 UPDATE B.table1 SET pk = pk + @increment WHERE pk IS NOT NULL 
   ORDER BY pk DESC;

(The where clause is to stop MySQL from giving an error in strict mode)

If you write a script with those two lines per table in your database you can then insert all tables into database AA, remember to disable foreign key checks during the update with

SET foreign_key_checks = 0;
... do lots of inserts ...
SET foreign_key_checks = 1;

Good luck.

Create a new database table with an autoincrimented primary key as the first column. Then add the column names from your databases and import each one. Then just drop the old primary field, and rename the new one to match your primary name.