Tim M Tim M - 1 year ago 71
MySQL Question

Merge two identical tables that has a primary key

This seems like a simple task, but I am struggling to find a way to do this. I have two tables (A and B) with the same structure. Both have an auto increment primary key. I want the data from table B to go in to table A. I tried

insert into A select * from B

However I get a error that primary key already exists. I would like the new rows from table B to get a new primary key when I insert them in to table A - so basically discard the primary key from B but insert all the other columns. Seems like it should be a simple query but I can't figure it out. Thank you.

Answer Source

You're also selecting (and inserting) the auto_increment id, which fails if such id already exists. To let mysql automatically assign id just select and insert all values besides the id:

INSERT INTO A (foo, bar, baz)
SELECT foo, bar, baz FROM B