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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close