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

Try this:

SELECT * INTO #t FROM CTABLE WHERE CID = @cid
ALTER TABLE #t
DROP COLUMN CID

INSERT CTABLE --Notice that INTO is removed here.
SELECT top(1) * FROM #t
DROP TABLE #t

Test Script(Tested in SQL 2005):

CREATE TABLE #TestIDNT
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    TITLE   VARCHAR(20)
)

INSERT #TestIDNT
SELECT 'Cybenate'

Answer 2

If using SQL Server Management Studio and your problems you have too many fields to type them all out except the identity column, then right click on the table and click "Script table as" / "Select To" / "New Query Window".

This will provide a list of fields that you can copy & paste into your own query and then just remove the identity column.