Post reply

Primary Key Values

  • February 6, 2009 at 10:10 am

    #128659

    I have a problem with the value that is being automatically generated for the LineItemID field, which is the primary key for the LineItem table.

    The LineItem table stores current data, and data from all previous days is then moved to the LineItemHistory table. The same LineItemID field exists in both tables, and should be unique in both tables (when the LineItem.LineItemID is generated, it shouldn't exist in either tables).

    Prior to doing a truncate on the LineItem table last week, this was working without a problem. The values for LineItemID seem to have been reset, and are now causing duplicate IDs to be generated, which causes a problem when the data is moved from the current table to the history table. I need to change the values that are being automatically generated by SQL and assigned to LineItem.LineItemID (I want to change it to assign numbers greater than the largest ID that is currently in either table, which is approximately 240000000).

    The data that is inserted into the LineItem table comes from another application, and the LineItemID is generated automatically by SQL. I cannot change the insert commands that are used by the application, so I need to change the numbers that SQL considers valid as options that will be automatically generated as the new records are inserted.

    Any help would be much appreciated!

  • February 6, 2009 at 10:15 am

    #939659

    DBCC CHECKIDENT (LineItem, RESEED, 240000000)

  • February 6, 2009 at 10:39 am

    #939670

    if you need to fix the LineItem table prior to archiving it, you might want to do something like

    UPDATE LineItem SET LineItemID = LineItemID + 240000000

    that, by coincidence, will also make the identity.nextvalue be the next value higher than exists in the table as well.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 6, 2009 at 10:42 am

    #939672

    Thank you! I've been told elsewhere this morning that it isn't possible.

    This worked, and my issue is resolved. Thanks again!

  • February 6, 2009 at 10:50 am

    #939679

    Keep in mind in the future that TRUNCATE TABLE will reset the Identity property back to the seed. DELETE FROM TABLE will maintain the current Identity.

    "Got no time for the jibba jabba!"
    -B.A. Baracus

  • February 6, 2009 at 11:42 am

    #939704

    Truncate may be a good choice as it's not logged and faster, assuming there is a lot data.

    As truncate will reset your Identity value to seed

    After copied the data into your hsitory table and truncate your primary table,

    you could then run this.

    Declare @maxid int

    Select @maxid=Max(lineitemid) from Historytable

    DBCC CHECKIDENT(yourtable, RESEED, @maxid+1)

    Or simply make the whole thing into a job or one store proc to achieve your goals.

Viewing 6 posts - 1 through 5 (of 5 total)

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