How can I merge two MySql tables?

Question

How can I merge two MySql tables that have the same structure? The primary keys of the two tables will clash, so I have take that into account.

Answer 1

If you need to do it manually, one time:

First, merge in a temporary table, with something like:

create table MERGED as (select * from table 1 UNION select * from table 2)

Then, identify the primary key constraints with something like

SELECT COUNT(*), PK from MERGED GROUP BY PK HAVING COUNT(*) > 1

Where PK is the primary key field...

Solve the duplicates.

Rename the table.

Answer 2

INSERT
INTO    first_table f
SELECT  *
FROM    second_table s
ON DUPLICATE KEY UPDATE
SET s.column1 = DO_WHAT_EVER_MUST_BE_DONE_ON_KEY_CLASH(f.column1)

Answer 3

It depends on the semantic of the primary key. If it's just autoincrement, then use something like:

insert into table1 (all columns except pk)
select all_columns_except_pk 
from table2;

If PK means something, you need to find a way to determine which record should have priority. You could create a select query to find duplicates first (see answer by cpitis). Then eliminate the ones you don't want to keep and use the above insert to add records that remain.

Answer 4

You can also try:

INSERT IGNORE
  INTO table_1 
SELECT *
  FROM table_2
     ;

which allows those rows in table_1 to supersede those in table_2 that have a matching primary key, while still inserting rows with new primary keys.

Alternatively,

REPLACE
   INTO table_1
 SELECT *
   FROM table_2
      ;

will update those rows already in table_1 with the corresponding row from table_2, while inserting rows with new primary keys.