Reset Identity Column Value in SQL Server depending on whats being inserted

Question

I have a SQL Server 2008 database with a table that has an ID field that has an identity increment and seed of 1.

This work great as each new record created automatically gets a new and distinct number.

Now when I insert certain new records I need to be able to make that value start at 10001 and then the next would be 10002 etc.

But this is only for certain inserts.

Is there a way to control the identity seed & increment on the fly via a query maybe?

So some inserts would follow 1,2,3,4,5,6,7 depending on whats being inserted

and others would follow 10001,10002,10003 depending on whats being inserted.

is this possible?

Dan_Bracuk · Accepted Answer

Make it a different field. Don't mess with your PK.Make sure you have a plan for when you get 10000 records in this table.

Sign up