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.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading