INSERT INTO othertbl SELECT * tbl

Current situation:

  INSERT INTO othertbl
    SELECT *
    FROM tbl       
  WHERE id = '1'

So i want to copy a record from tbl to othertbl. Both tables have an autoincremented unique index. Now the new record should have a new index, rather then the value of the index of the originating record else copying results in a index not unique error.

A solution would be to not use the * but since these tables have quite some columns i really think it's getting ugly.

So,.. is there a better way to copy a record which results in a new record in othertbl which has a new autoincremented index without having to write out all columns in the query and using 开发者_StackOverflowa NULL value for the index.

-hope it makes sense....-

Think you're gonna have to drop the * and specify the columns fella

If you're using SQL Server you could get a list of columns using

SELECT column_name+', ' from INFORMATION_SCHEMA.COLUMNS where table_name = 'tbl'

Building an insert statement using the result from the above should be easy.

You can dump the list of non-auto-increment columns for your table with this query, and then use it in the insert and select statements:

SELECT group_concat(column_name) 
from INFORMATION_SCHEMA.COLUMNS 
where table_schema = 'myschema' 
and table_name = 'tbl' 
and extra != 'auto_increment';

Try:

INSERT ...
    SELECT *
        FROM tbl
        ON DUPLICATE KEY UPDATE `id`=NULL

http://dev.mysql.com/doc/refman/5.1/en/insert-select.html