MySQL - INSERT INTO xxx SELECT with AUTO_INCREMENT column

Question

I have an old_table and a new_table:

CREATE TABLE `old_table` (
        `id` bigint(20) NOT NULL AUTO_INCREMENT,
        `col1` varchar(15) DEFAULT NULL,
        `col2` int(15) DEFAULT NULL,
        ...,
        PREMARY_KEY (`id`)
);

CREATE TABLE `new_table` LIKE `old_table`;

Then both tables are populated with some values. After that, I want to select some rows from old_table and insert into new_table:

INSERT INTO `old_table` SELECT * FROM `new_table` WHERE col2 > 100;

But this will cause errors due to duplicate key. I'm too lazy to specify the columns in the SELECT clause, because in the real system the tables have a lot of columns.

What would be the best way to get around the problem?

Answer 1

set @sql =  (select concat('insert into new_table SELECT NULL,',
             group_concat(column_name),' from ','old_table') from information_schema.columns
         where table_name = 'old_table' and table_schema = '<database>' and column_name != 'id'
             order by ordinal_position);

prepare stmt1 from @sql;
execute stmt1;
deallocate prepare stmt1;

where

<database> - your database

Select all columns except one in MySQL?

How to insert data from an EXECUTE statement in mySql?