SQL Server – How to Reseed Auto Increment to Specific Number

auto-incrementetlsql server

Is it possible to set the identity column of a table to start from a specific value?

I'm converting an ETL stage from mysql to a sql server database where a staging database loads records with a unique ID starting from the maximum value in the main database (which is not auto-incrementing). The mysql went like this:

SET @s = Concat('alter table table_1 auto_increment=',coalesce(@indexmaxt,0)+1);

Ideally this is what I want to do:

DBCC CHECKIDENT (table_1 , RESEED, @indexmaxt+1)

Is this possible or is this impossible in SQL server

Best Answer

This works, does that answer your question?

create table dbo.test (id int identity(1,1), value int);


declare @indexmaxt int = 9999,@indexmaxt2 int;
SET @indexmaxt2 = @indexmaxt+1;
DBCC CHECKIDENT ('dbo.test' , RESEED, @indexmaxt2);


insert into dbo.test(value) values(1);

select * from dbo.test;

Results: id 10000
value 1

Related Question