MySQL - INSERT INTO xxx SELECT with AUTO_INCREMENT column
auto-increment, insert, mysql, select
Solution
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?
Problem
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?