Reseeding Identities

I did a job today where I needed to to copy data from one table to another.  Both tables were exactly the same, with identity columns and the destination table empty.  In auto-pilot mode, I was about to write a script to copy the data across, capture the new identity numbers to have them mapped to the old one (much like in my previous post Capturing identity ID's from an insert or update).  But then of course I quickly realised that there was a much simpler way of doing this.  It didn't matter if the identity numbers were contiguous. I could just switch identity insert on, and retain all the values as they were, which also made it easier when I copied across data from other tables that linked on foreign keys.

A couple of things to take note of though, when doing this:

1. You can't just insert a value into an identity column.
2. When you DO insert values into an identity column, the values will be higher than the next number to be generated, which will fail due to a unique constraint.

So, you first need to enable identity insert.  This will, wait for it... allow you to insert values into an identity column. Duh, right?  Just make sure you switch it off again when you're done.  This is the statement that you need to run:

SET IDENTITY_INSERT {table_name} ON | OFF;

Another thing to be aware of in order to insert... you need to specify all of the column names in the insert statement.  There's no using select *, or relying on the order of columns in the destination  table.

So you've set identity_insert to on and inserted the data, probably using insert into select.  Remember that the value of the next identity number to be generated won't be affected by the insert and so will need to be changed.  This is done using the DBCC CHECKIDENT command, which can also be used to see what the current identity value is.  To reseed though, you will run the following:

DBCC CHECKIDENT ({table_name}, RESEED, n);

Where n is value of the next number you want to have generated minus 1.  So if the highest value of the records you've inserted is 666, then you will reseed the column to be 666, and the next number it generates will be 667.

---------------------
--Reseed Example--
---------------------
declare @maxID int;

set identity_insert destinationTable on; 

insert into destinationTable (ID, col1, col2, col3)
select (ID, col1, col2, col3)
from sourceTable; 

set identity_insert destinationTable off; 

select @maxID = max(ID) from destinationTable; 

DBCC CHECKIDENT (destinationTable, reseed, @maxID);