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?

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

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: