Q

Copy a row to the same table

I want to insert a new row in a table with the same row in the same table with a different primary key value. I think I can do it using a temporary table but is there any other way?

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 last published in January 2006

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close