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

Question

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.

Answer 1

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).