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

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.

Answer 2

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.