title: How to copy a row with every column except identity column (SQL Server 2005)
body: <p>My code:</p>
  
  <pre><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
  </code></pre>
  
  <p>The error is:</p>
  
  <pre><code>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.
  </code></pre>
  
  <p>And I did set </p>
  
  <pre><code>SET IDENTITY_INSERT CTABLE OFF
  GO
  </code></pre>