How to copy a row with every column except identity column (SQL Server 2005)

Question

My code:

SELECT * INTO #t FROM CTABLE WHERE CID = @cid   --get data, put into a temp table


ALTER TABLE #t
DROP COLUMN CID       -- remove primary key column CID


INSERT INTO CTABLE SELECT * FROM #t   -- insert record to table
DROP TABLE #t                                -- drop temp table

The error is:

Msg 8101,
An explicit value for the identity column in table 'CTABLE' can only 
be specified when a column list is used and IDENTITY_INSERT is ON.

And I did set

SET IDENTITY_INSERT CTABLE OFF
GO

Answer 1

DECLARE 
    @cid INT,
    @o   INT,
    @t   NVARCHAR(255),
    @c   NVARCHAR(MAX),
    @sql NVARCHAR(MAX);

SELECT
    @cid = 10,
    @t   = N'dbo.CTABLE',
    @o   = OBJECT_ID(@t);

SELECT @c = STRING_AGG(QUOTENAME(name), ',')
    FROM sys.columns
    WHERE [object_id] = @o
    AND is_identity = 0;

SET @sql = 'SELECT ' + @c + ' INTO #t 
    FROM ' + @t + ' WHERE CID = @cid;

    INSERT ' + @t + '('+ @c + ') 
    SELECT ' + @c + ' FROM #t;' 

PRINT @sql;
-- exec sp_executeSQL @sql,
--  N'@cid int',
--  @cid = @cid;

However it seems much easier to just build the following SQL and avoid the #temp table altogether:

SET @sql = 'INSERT ' + @t + '(' + @c + ') 
    SELECT ' + @c + ' FROM ' + @t + '
    WHERE CID = @cid;';

PRINT @sql;
-- exec sp_executeSQL @sql,
--  N'@cid int',
--  @cid = @cid;