Post reply

Modify the Auto Number

  • November 21, 2006 at 12:15 pm

    #114750

    Hai

     

        I have a table with auto number in Sql Server.  Is that possible to modify that number. Like (EmployeeID)

    Thanks

     

     

      

  • November 21, 2006 at 12:20 pm

    #673290

    Yes and no... what do you need exactly?

  • November 22, 2006 at 11:23 am

    #673548

    dbcc checkident('tablename',reseed,<seednum&gt


    PeteK
    I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.

  • November 22, 2006 at 11:27 am

    #673549

    If that doesn't help him, it may help someone else someday. .

  • November 24, 2006 at 4:53 pm

    #673870

    Yes, it is possible to modify the number by first doing a SET IDENTITY INSERT... here's the syntax from Books Online...

    SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }

    Don't forget to turn it off when you've completed your update.  And, don't forget that it is usually a bad to change the value in an identity column especially if it is a Primary Key... real bad idea...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 25, 2006 at 9:22 am

    #673892

    Hai Jeff

       Thanks a lot. It worked out .

       By the way why you say that changing the IDENTITY_INSERT  value is bad ..?  Is there any reasons. .. ?

     

     

    Webjalam 

  • November 25, 2006 at 9:40 am

    #673894

    If it's the Primary Key, then you may be (will be) destroying referential integrity for other data in other tables.  If it's the Primary Key and you don't have referential integrity, then you are in very deep Kimchie... ... that would be very bad database design because you have no guarantee that rows can be made to match up in the future.  Think of it like this... you have a bunch of customers in a table that assigns CustID's using the IDENTITY property of the CustID column... you have a process that accepts payments for customers based on the CustID in the Customer table... you change the CustID in the Customer table... if referential integrity is not in place to keep you from doing that, you've just orphaned all the payments those customers made and there's no way to get back.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 25, 2006 at 5:13 pm

    #673918

    well you can ask all those missing payments to be remade... but it might not be a good thing on the long run for the company .

  • November 26, 2006 at 4:32 pm

    #673957

    Yeah... especially since I might use my pickup to pull a santion out of the building just to get their attention

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply