Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Posts
    2

    Question Unanswered: 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.
    Last edited by jctrips; 05-22-03 at 18:45.

  2. #2
    Join Date
    Feb 2003
    Location
    Brisbane, Australia
    Posts
    110

    Re: Reset Autonumber Value in MS SQL 2k

    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.

  3. #3
    Join Date
    May 2003
    Posts
    2

    Re: Reset Autonumber Value in MS SQL 2k

    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?

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    DBCC CHECKIDENT ('table_name', RESEED, new_reseed_value) can be specified as per the parameters you accept.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •