1. Knowledge Base
  2. >
  3. Results
  4. >
  5. Merge two MySQL databases

Merge two MySQL databases

 

The easiest way to explain this is to show you how to copy data from one table to another.

Both databases need to exist on the same account. If they do not, you will need to create a backup of one and move it. (If both databases have the same name, you will need to also rename one before moving).

Once both are on the same account, login to either SSH or cPanel.


cPanel

  1. Log in to cPanel.
  2. In the Databases section, click the phpMyAdmin icon.

    HostGator cPanel phpMyAdmin

  3. Click the SQL tab at the top.

    HostGator cPanel phpMyAdmin SQL Tab

  4. You will see where it says, Run SQL query/queries on server "localhost":.

    HostGator cPanel phpMyAdmin Run SQL query/queries on server

  5. Insert the following code in the text box below, but replace DB1 and DB2 with the database names. Also, replace TABLE1 with the table name you are trying to merge.
    INSERT INTO DB1.TABLE1 SELECT * FROM DB2.TABLE1
  6. Click the Go button.
  7. Repeat for any other tables you want to merge.


Shell (SSH)

  1. From SSH, you need to type the command to access MySQL. Here is the format, but replace MYNAME with your username and PASS with your password.
    MySQL -u MYNAME -pPASS
  2. Now type the following code, but replace DB1 and DB2 with the database names. Also, replace TABLE1 with the table name you are trying to merge.
    INSERT INTO DB1.TABLE1 SELECT * FROM DB2.TABLE1;
  3. Hit the Enter key.
  4. Repeat for any other tables you want to merge.

Here is what mine looks like:

INSERT INTO myname_oldsaved.responses SELECT * FROM myname_newsaved.responses

HINT: Use REPLACE INTO instead of INSERT INTO to have the data from DB2 overwrite the data in DB1 if they share a primary/unique key.