Merge Two Databases MySQL

Discussion in 'MySQL' started by jfontestad, May 28, 2009.

0
  1. #1
    Hello,

    I have a problem. I am trying to merge two database (mySQL) into one. Easy right? Well my problem comes because both databases have uniqueID fields. One of the databases was an old one and I scratched it and restarted a new one, but now I want to merge both and just want to append the new database to the end of the old database, yet I don't want to go through 2200+ records to change that uniqueID to where it is will be sequential with the end of the old database.

    Is there anything that can be done?
     
    jfontestad, May 28, 2009 IP
  2. Social.Network

    Social.Network Member

    Messages:
    517
    Likes Received:
    15
    Best Answers:
    0
    Trophy Points:
    35
    #2
    Yes, there are several options. I have a couple of questions, is the unique id column an auto-generated primary key? Also, are there foreign key constraints to the unique id column in other tables? I will recommend an approach based on your specific situation.
     
    Social.Network, May 28, 2009 IP
  3. jfontestad

    jfontestad Well-Known Member

    Messages:
    1,236
    Likes Received:
    20
    Best Answers:
    0
    Trophy Points:
    148
    #3
    Hey,

    Yes the uniqueID is auto generated primary key. In the dump of the new database it starts at 1 again and continues to 2200+. The old database starts at 1 as well and I don't want to replace the old records with the new ones.
     
    jfontestad, May 29, 2009 IP
  4. Social.Network

    Social.Network Member

    Messages:
    517
    Likes Received:
    15
    Best Answers:
    0
    Trophy Points:
    35
    #4
    I assume there are no foreign key constraints to the primary key in other tables, so here is one approach. Create an empty database with the same structure then copy all of the records from both databases to the new one. It will regenerate the primary keys for you. When you copy them over, you need to exclude the existing primary key of course. I hope that makes sense, else I can write some samples for you.

    Example:

    Database #1 (Source)
    1,'A','B','C','D'
    2,'E','F','G','H'
    3,'I','J','K','L'

    Database #2 (Source)
    1,'M','N','O','P'
    2,'Q','R','S','T'

    Database #3 (Target)
    1,'A','B','C','D'
    2,'E','F','G','H'
    3,'I','J','K','L'
    4,'M','N','O','P'
    5,'Q','R','S','T'

    Good Luck.
     
    Social.Network, May 29, 2009 IP
  5. alfa_375

    alfa_375 Active Member

    Messages:
    445
    Likes Received:
    5
    Best Answers:
    0
    Trophy Points:
    60
    #5
    I totally agree with socialnetwork. This is the best idea. Just copy the old database and new database into the third database without the primary key.

    INSERT INTO third_db.table SELECT col2, col3,col4 FROM old_db.tablename;
    INSERT INTO third_db.table SELECT col2, col3,col4 FROM new_db.tablename;

    You can replace col2, with the your column/field names.

    Hope this will help.

    Regards
     
    alfa_375, Jun 1, 2009 IP