title: SQL Server : copy row and Insert into to the same table but with a different ID
body: <p>I would just like to copy a specific <code>LOAD_NO</code> from a table called <code>LOADS</code> and insert it back into the same <code>LOADS</code> table but without using the old <code>LOAD_NO</code> (aka <code>ID</code>)....</p>
  
  <p>The tables name is <code>LOADS</code>.</p>
  
  <ol>
  <li><p>I would like to copy a specific row and insert it into the table but without needing to specify all of the fields (because the table has a lot of fields)</p></li>
  <li><p>I need to exclude the field named: <code>LOAD_NO</code></p></li>
  <li><p>Then delete the old row</p></li>
  </ol>
  
  <p>I tried something like this:</p>
  
  <p>I cloned the <code>LOADS</code> table without cloning the data and I named that table <code>LOADS_TempTable</code> and added the identity of <code>LOAD_NO</code> seed start at: <code>11600</code> </p>
  
  <pre><code>SELECT * 
  INTO LOADS_TempTable 
  FROM LOADS WHERE LOAD_NO = 200;
  
  ALTER TABLE LOADS_TempTable DROP COLUMN LOAD_NO;
  
  INSERT INTO LOADS 
     SELECT * 
     FROM LOADS_TempTable;
  
  DELETE FROM LOADS 
  WHERE LOAD_NO = 200;
  </code></pre>
  
  <p>Here is how the column <code>LOAD_NO</code> in the table <code>LOADS</code> looks:</p>
  
  <p><img src="https://i.stack.imgur.com/tTJ29.png" alt="enter image description here"></p>
  
  <p>The error I get says the following:</p>
  
  <blockquote>
    <p>Msg 8101, Level 16, State 1, Line 3<br>
    An explicit value for the identity column in table 'LOADS' can only be specified when a column list is used and IDENTITY_INSERT is ON.</p>
  </blockquote>
  
  <p>I tried to add: </p>
  
  <pre><code>set identity_insert LOADS OFF
  GO
  </code></pre>
  
  <p>and also</p>
  
  <pre><code>set identity_insert LOADS ON
  GO
  </code></pre>
  
  <p>I always get the same error.</p>
  
  <p>THANKS FOR ANY HELP :)</p>