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

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top