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

Question

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

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

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.

Specify all fields but your ID field.

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

I have the same issue where I want a single script to work with a table that has columns added periodically by other developers. Not only that, but I am supporting many different versions of our database as customers may not all be up-to-date with the current version.

I took the solution by Jonas and modified it slightly. This allows me to make a copy of the row and then change the primary key before adding it back into the original source table. This is also really handy for working with tables that do not allow NULL values in columns and you don't want to have to specify each column name in the INSERT.

This code copies the row for 'ABC' to 'XYZ'

SELECT * INTO #TempRow FROM SourceTable WHERE KeyColumn = 'ABC';
UPDATE #TempRow SET KeyColumn = 'XYZ';
INSERT INTO SourceTable SELECT * FROM #TempRow;
DELETE #TempRow;

Once you have finished the drop the temp table.

DROP TABLE #TempRow;

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)