How do you copy a record in a SQL table but swap out the unique id of the new row?

Kilhoffer_5469 0
Viewed 201157

This question comes close to what I need, but my scenario is slightly different. The source table and destination table are the same and the primary key is a uniqueidentifier (guid). When I try this:

insert into MyTable
    select * from MyTable where uniqueId = @Id;

I obviously get a primary key constraint violation, since I'm attempting to copy over the primary key. Actually, I don't want to copy over the primary key at all. Rather, I want to create a new one. Additionally, I would like to selectively copy over certain fields, and leave the others null. To make matters more complex, I need to take the primary key of the original record, and insert it into another field in the copy (PreviousId field).

I'm sure there is an easy solution to this, I just don't know enough TSQL to know what it is.

sql-server-2005 tsql sql-server sql
11 Answers

Try this: 


insert into MyTable(field1, field2, id_backup)
    select field1, field2, uniqueId from MyTable where uniqueId = @Id;

Any fields not specified should receive their default value (which is usually NULL when not defined).

user_169110

I'm guessing you're trying to avoid writing out all the column names. If you're using SQL Management Studio you can easily right click on the table and Script As Insert.. then you can mess around with that output to create your query.

Matt_Hinze_26760

Specify all fields but your ID field.

INSERT INTO MyTable (FIELD2, FIELD3, ..., FIELD529, PreviousId)
SELECT FIELD2, NULL, ..., FIELD529, FIELD1
FROM MyTable
WHERE FIELD1 = @Id;
Scott_Bevington_95440

I know my answer is late to the party. But the way i solved is bit different than all the answers.

I had a situation, i need to clone a row in a table except few columns. Those few will have new values. This process should support automatically for future changes to the table. This implies, clone the record without specifying any column names.

My approach is to,

  1. Query Sys.Columns to get the full list of columns for the table and include the names of columns to skip in where clause.
  2. Convert that in to CSV as column names.
  3. Build Select ... Insert into script based on this.


declare @columnsToCopyValues varchar(max), @query varchar(max)
SET @columnsToCopyValues = ''



--Get all the columns execpt Identity columns and Other columns to be excluded. Say IndentityColumn, Column1, Column2
Select @columnsToCopyValues = @columnsToCopyValues  + [name] + ', ' from sys.columns c where c.object_id = OBJECT_ID('YourTableName') and name not in ('IndentityColumn','Column1','Column2')
Select @columnsToCopyValues = SUBSTRING(@columnsToCopyValues, 0, LEN(@columnsToCopyValues))
print @columnsToCopyValues



Select @query = CONCAT('insert into YourTableName (',@columnsToCopyValues,', Column1, Column2) select ', @columnsToCopyValues, ',''Value1'',''Value2'',', ' from YourTableName where IndentityColumn =''' , @searchVariable,'''')



print @query
exec (@query)

user_7128260 
insert into MyTable (uniqueId, column1, column2, referencedUniqueId)
select NewGuid(), // don't know this syntax, sorry
  column1,
  column2,
  uniqueId,
from MyTable where uniqueId = @Id
Jeffrey_L_Whitledge_101740

If "key" is your PK field and it's autonumeric.

insert into MyTable (field1, field2, field3, parentkey)
select field1, field2, null, key from MyTable where uniqueId = @Id

it will generate a new record, copying field1 and field2 from the original record

eduardo_120910

You can do like this: 

INSERT INTO DENI/FRIEN01P 
SELECT 
   RCRDID+112,
   PROFESION,
   NAME,
   SURNAME,
   AGE, 
   RCRDTYP, 
   RCRDLCU, 
   RCRDLCT, 
   RCRDLCD 
FROM 
   FRIEN01P

There instead of 112 you should put a number of the maximum id in table DENI/FRIEN01P.

user_2955170

Here is how I did it using ASP classic and couldn't quite get it to work with the answers above and I wanted to be able to copy a product in our system to a new product_id and needed it to be able to work even when we add in more columns to the table.

Cn.Execute("CREATE TEMPORARY TABLE temprow AS SELECT * FROM product WHERE product_id = '12345'")
Cn.Execute("UPDATE temprow SET product_id = '34567'")
Cn.Execute("INSERT INTO product SELECT * FROM temprow")
Cn.Execute("DELETE temprow")
Cn.Execute("DROP TABLE temprow")
user_94506140
Related