title: SQL Server Identity Column Renumbering
body: <p>I have a table which has a primary key ID column and is set to increment 1 for each new entry, a fairly standard configuration. This table is updated regularly with new records, and is archived once a month to an archive table and then truncated.  </p>
  
  <p>Under normal operation the next record to go into the table after truncation would be whatever the next ID value is, ensuring that there is no overlap in IDs between the archive table and the "live" table.  </p>
  
  <p>Due to unknown factors, somehow the ID column in the live table "reverted" back to an ID that has already been used. Because this database was designed poorly, now I've got some data integrity issues.  </p>
  
  <p>My solution to resolve the issue is to update the ID column for all records in the "live" table so there will be no overlap with the ID's that exist in the "archive" table.  Once I update the IDs I will reseed the Identity column so that future records will also not overlap.</p>
  
  <p>My question is not about reseeding the column (using DBCC -- I know how to do this) but about how to go about re-numbering the existing records with new ID values.  Should I do this with a looped dynamic SQL query (delete record, insert record), or is there some SQL Server function I am unaware of that can automatically renumber the ID column?</p>