SQL Server Identity Columns – Inserting and Resetting

Posted on Tuesday, September 27th, 2011 at 8:20 pm in

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.

Add your comment

Leave a Reply

Top