Moving rows between MySQL tables

I have two table开发者_运维百科s with the same structure, and both contain an ID field of auto_increment. I would like to move a row from one table to the other.

I know I can use:

INSERT INTO `new_table` VALUES (SELECT * FROM `old_table`)

But that way the ID in new_table will receive the ID of old_table. So I would basically want to copy all fields except for the ID, which needs to get the auto_increment value of new_table. How can I do that?

Thanks!

Joel

EDIT:

Sorry, I should have mentioned in my original post - I would like a way to do it without selecting each field specifically, mostly since I have a lot of fields... I am looking for a more generic approach.

Thanks!

INSERT INTO `new_table` (name, surname, email) 
  (SELECT name, email, surname FROM `old_table`);

Be careful though when letting the MySQL automatically assign new IDs in case there are relations from other tables you need to move as well.

In such cases I'll move the whole data around by adding a known offset to the ID which will be large enough to avoid ID collisions in the target table. Since the offset is known I'll be able to apply the same offset when moving relations (to the target table) from other tables.

Edit based on question edit: I highly suggest to avoid "the generic approach". It may seem pedantic having to write all the fields but it's the only way to avoid mistakes and, of course, the ID collision problem. If you want to avoid typing the fieldnames (since it's boring and error prone) you can copy paste the fieldnames from show create table new_table.

Do not select id in that case:

INSERT INTO `new_table` (`field2`, `field3`) VALUES (SELECT `field2`, `field3` FROM `old_table`)

Speciy the column names in both INSERT list and SELECT list excluding the ID column

INSERT INTO `new_table` (COLS-I-WANT) 
VALUES (SELECT (OLD-COLS-I-WANT) FROM `old_table`)

Hope this helps.