How do you copy a record in a SQL table but swap o

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

Specify all fields but your ID field.

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

Answer 2

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

Answer 3

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.

Answer 4

Ok, I know that it's an old issue but I post my answer anyway.

I like this solution. I only have to specify the identity column(s).

SELECT * INTO TempTable FROM MyTable_T WHERE id = 1;
ALTER TABLE TempTable DROP COLUMN id;
INSERT INTO MyTable_T SELECT * FROM TempTable;
DROP TABLE TempTable;

The "id"-column is the identity column and that's the only column I have to specify. It's better than the other way around anyway. :-)

I use SQL Server. You may want to use "CREATE TABLE" and "UPDATE TABLE" at row 1 and 2. Hmm, I saw that I did not really give the answer that he wanted. He wanted to copy the id to another column also. But this solution is nice for making a copy with a new auto-id.

I edit my solution with the idéas from Michael Dibbets.

use MyDatabase; 
SELECT * INTO #TempTable FROM [MyTable] WHERE [IndexField] = :id;
ALTER TABLE #TempTable DROP COLUMN [IndexField]; 
INSERT INTO [MyTable] SELECT * FROM #TempTable; 
DROP TABLE #TempTable;

You can drop more than one column by separating them with a ",". The :id should be replaced with the id of the row you want to copy. MyDatabase, MyTable and IndexField should be replaced with your names (of course).