How to re-seed a table identity in SQL Server 2008 and undo it all safely?

user_231677 0
Viewed 91867

I need to do this for testing only, but then undo it when the test is done.

I have seen some tutorials online on how to re-seed a table, but not so much on how to undo it.

Let's say the table definition is the following:

create table beer
(
 beer_id  numeric(10) not null,
 mnemonic        nvarchar(8)
);
go

Let's say that I want the new identities to temporarily start at 12345, and at the end delete the new rows and set the next identity to what it would have been.

seed sql-server-2008
2 Answers

Sometimes we have Schema restriction from some users and thus causes error (Cannot find a table or object with the name "TableName". Check the system catalog), hence the best thing is to follow the below code.

Schema.TableName should be enclosed in apostrophe

DECLARE @SeedValue INT
SET @SeedValue = (SELECT MAX(ColumnName) FROM Schema.TableName)
DBCC CHECKIDENT ('Schema.TableName',RESEED,@SeedValue)
user_61752250
Related