SQL Server Identity Columns – Inserting and Resetting
So I wanted to test a scenario against a database table which had no rows, even though there were several currently in the table. Easy you say, just copy the data into a ‘backup’ table, delete the rows, run your tests, then copy the data back. Well not so fast when you have an IDENTITY column defined in the table.
The first step is still simple and straightforward.
SELECT id , code , description INTO backup_Widget FROM Widget
This copies the rows from Widget into a new table backup_Widget. The new table columns have the same datatypes as the source table.
Now remove the rows with
DELETE FROM Widget
Now if you try to ‘return’ the rows from the backup table to the regular table
INSERT INTO Widget ( id ,code ,description) SELECT id ,code ,description FROM backup_Widget
You the an error: “Cannot insert explicit value for identity column in table ‘Widget’ when IDENTITY_INSERT is set to OFF.”
Now, if you don’t care what values are in the IDENTITY column you can just re-run the insert but without the reference to the id column. Also, if you have sequential IDENTITY values without any gaps you can simply TRUNCATE the table, which resets the IDENTITY seed value, and re-run the insert with an “ORDER BY id” clause.
If you have gaps in the IDENTITY values and must restore the data with exactly the same ids you need to do something like this.
set IDENTITY_INSERT dbo.Widget ON GO INSERT INTO Widget ( id ,code ,description) SELECT id ,code ,description FROM backup_Widget GO
Now the data is restored with the same IDENTITY values it had originally.
There are a few other commands of interest when dealing with IDENTITY values.
To check the current IDENTITY value for a table:
DBCC CHECKIDENT(Widget)
Which gives you something like: Checking identity information: current identity value ’12’, current column value ’12’
To ‘reseed’ the table which means give the table a new seed value:
DBCC CHECKIDENT(Widget, RESEED, 20)
Gives you something like: Checking identity information: current identity value ’12’, current column value ’20’.
Since the ‘current column value’ shows the last used seed value, the next record inserted into the Widget table will have an id of ’21’ (the default increment being 1). You can omit the reseed value on the statement and the new seed value will be the maximum value in the IDENTITY column if the current seed value is less than this.
