Primary Key Merge and Update

Question

I looked, but did not find a solution that looked like a simple problem.

I have many tables with the same structure and you want to combine them. The only problem is that they all have an id field as their primary key. There will be many duplicates in the primary key. It doesn't matter what the identifier ends with. How can I join all the tables so as not to lose any data?

Answer 1

Create an AUTO_INCREMENT field in the new table and omit it from the list of values:

 CREATE TABLE NEW_TABLE (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, col1 …, col2 …, …); INSERT INTO new_table (col1, col2, …) SELECT col1, col2 FROM old_table_1 UNION ALL SELECT col1, col2 FROM old_table_2 UNION ALL … 

Answer 2

In the destination table, the auto-increment field is used as the primary key. Then copy all columns from tables other than the primary key into the new table.

 INSERT new_table (col1, col2 , col3) SELECT col1, col2, col3 FROM old_table