I've got a table with >100 columns, with an identity column as the first
(each record contains a set of setting values for external hardware, and it
has over 100 configurable settings, so...).

Is there a way to quickly copy all values from one existing record into
another except for the identity? (in SQL Server management Studio, OSQL,
SQLCMD or anything similar)


I thought I could copy the source record into a temporary table, change its
identity column value to the target value, remove the original target
record, and insert the record from the temporary table with IDENTITY_INSERT
on - but INSERT INTO requires a column list if there's an identity column,
and it seems you can't use subqueries if there's a column list.


This clearly doesn't work (names edited for clarity):

select * into ##temptable from mytable where identitycolumn = 1
update ##temptable set identitycolumn = 2
delete from mytable where identitycolumn = 2
set IDENTITY_INSERT mytable ON
insert into mytable select * from ##temptable
set IDENTITY_INSERT mytable OFF

"An explicit value for the identity column in table 'mytable' can only be
specified when a column list is used and IDENTITY_INSERT is ON."


But this doesn't work either:

insert into mytable (column list) select * from ##temptable
--> Cannot update identity column (I get this despite IDENTITY_INSERT ON)

insert into mytable (column list) VALUES (new_identity_val, (select ...))
--> Subqueries not supported


This works, but it's clumsy, time consuming and typing error prone:

delete from mytable where identitycolumn=2
insert into mytable (identitycolumn) values (2)
.... and open the table and type over all other non-default values manually.