tags:

views:

58

answers:

4

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 a NULL value for the index.

-hope it makes sense....-

+3  A: 

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

Paul
As a matter of practice, even though it results in big, ugly statements, you oughtta do that anyway, IMO. Changes in structure are less likely to surpise you in bad ways.
DaveE
Was afraid of that :S Oh well. I'll probably to just that to keep things moving OR might go for Ike Walkers solution to retrieve needed column names and take it from there.Thanx either way guys!
Harry
A: 

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.

Raj
Oops, didn't notice the mysql tag.
Raj
No worries mate. You meant well. Besides, Ike came up with the similar solution for mysql. So thanx to the both of you!
Harry
A: 

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';
Ike Walker
Just Wow!I dont think its gonna make my script look any better, but this query actually does pull up the column names on the fly :D
Harry
A: 

Try:

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

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

el.pescado