title: Alter exisitng int column to identity in sybase
body: <p>Sybase 12.5</p>
  
  <p>I have an existing table in production that needs it's PK int column to be altered such that it is auto populated - when the table was created it would ideally have had the ID column created as an Identity.  This ID column is a foreign key in multiple other tables so deleting the table and starting again isn't an option.  Problem is, I can't set the PK as an IDENTITY, and creating a temp column with the current values and copying these to a new IDENTITY column is also failing.</p>
  
  <p>As the ID column is already populated, I can't simply turn this column into an IDENTITY (at least, I haven't found any SQL that will do this).</p>
  
  <p>I have created a copy of the table, with an additional column holding the current PK value (division_id_tmp).</p>
  
  <pre><code>CREATE TABLE division_tmp
  (
     division_id int IDENTITY NOT NULL 
     division_id_tmp int,      
     description varchar(255) NOT NULL,
     is_active tinyint DEFAULT 1 NOT NULL,
  )
  </code></pre>
  
  <p>When i try to enter a datascript from the original table</p>
  
  <pre><code>INSERT INTO division_tmp
  (description,is_active,division_id_tmp,division_id) 
  VALUES 
  ('TEST',1,36,34)
  </code></pre>
  
  <p>I get the following error:</p>
  
  <pre><code>Error: Explicit value specified for identity field in table 'division_tmp' 
  when 'SET IDENTITY_UPDATE' is OFF.
  </code></pre>
  
  <p>If I run the statement:</p>
  
  <pre><code>SET IDENTITY_INSERT division_tmp OFF
  </code></pre>
  
  <p>The statement executes without complaint, but attempts to insert the datascript result in the same error as above.  If I run the statement</p>
  
  <pre><code>SET IDENTITY_INSERT ac_division_lookup_awd ON
  </code></pre>
  
  <p>I get the error</p>
  
  <pre><code>Error: Unable to 'SET IDENTITY_INSERT' for table '**division_tmp**' because 
  IDENTITY_INSERT or IDENTITY_UPDATE is already ON for the table '**division**' in 
  database 'preserve'.
  </code></pre>
  
  <p>I can't be the first person who's experienced this problem?  Any ideas on where I'm going wrong?</p>
  
  <p>Thanks</p>