Reset Autonumber Value in MS SQL 2k

I am learning the differences between Access and SQL the hard way.

SQL will not simply allow me, through an append query. to reset the starting value of my issueing ID field (autonumber). The new value is always higher than the last sequenced range.

I do not have experience with triggers, and from what I am reading this may be my solution.

Basically, I have a reference table that issues ID's when an ID is needed (table B). I only know the current range (min and max) numbers. Once this range is completely allocated. A new range of numbers is entered. New ranges can be 700 IDs or 7000 IDs.. never know until they are issued from other source.

How to reset the value for my current autonumber (increment) field in SQL.I am assuming that you are referring to an identity column, if so have a read about: DBCC CHECKIDENT in BOL.

Hope that helps.

Originally posted by jctrips
I am learning the differences between Access and SQL the hard way.

SQL will not simply allow me, through an append query. to reset the starting value of my issueing ID field (autonumber). The new value is always higher than the last sequenced range.

I do not have experience with triggers, and from what I am reading this may be my solution.

Basically, I have a reference table that issues ID's when an ID is needed (table B). I only know the current range (min and max) numbers. Once this range is completely allocated. A new range of numbers is entered. New ranges can be 700 IDs or 7000 IDs.. never know until they are issued from other source.

How to reset the value for my current autonumber (increment) field in SQL.|||THis definitely helps for identifying the seed. And I can see how to RESEED.

But how now to automate this based on new Range inputted by Admin User on Front End?|||DBCC CHECKIDENT ('table_name', RESEED, new_reseed_value) can be specified as per the parameters you accept.