Hi, I want to insert a new row in a table with the same row in the same table with a different primary key value. Can you please tell me a different way to do it? I think I can do it using a temporary table but is there any other way?
We need two things to accomplish this: the primary key value of the row we wish to copy, and the primary key value of the row we wish to insert. Let's say these values are 9 and 37 respectively, i.e., we want to copy the row with the primary key value of 9, and we want to insert this row of data into the table with a value of 37 for the primary key.
insert into mytable ( pkey, data1, data2, data3 ) select 37 , data1, data2, data3 from mytable where pkey = 9
If the primary key happens to be an auto_increment or identity column (depending on which database system you're using), we may be happy to allow the new primary key value to be assigned automatically.
insert into mytable ( data1, data2, data3 ) select data1, data2, data3 from mytable where pkey = 9
In this case we don't mention the primary key column while inserting.
This was first published in January 2006