Reseed Identity

You may have the need to manually insert a constant value in a column that has an identity, while still using the identity going forward. For example, suppose you have a Persons table, where PersonID is an auto-generated identity. But you need a few constant PersonID values representing a dummy account or admin account, like PersonID=10000000. If you just insert the new value, you'll mess up the identity seed for all future records. Instead, get the current value, then insert your record, then re-seed the identity.

create table dbo.tbl (i int identity);

-- i=1, i=2
insert tbl default values;
insert tbl default values;

declare @newseed int;
select @newseed = ident_current('dbo.tbl'); -- 2

set identity_insert tbl on;

-- i=999999999
insert tbl (i) values (999999999);

set identity_insert tbl off;

dbcc checkident ('dbo.tbl', reseed, @newseed);

-- i=3, i=4
insert tbl default values
insert tbl default values

-- 1,2,3,4,999999999
select * from dbo.tbl;

drop table dbo.tbl;