Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Posts
    97

    Unanswered: Dupe identity values

    I'm running into the weirdest thing I've seen.

    I have one table with a bigint identity field as the PK (call it the "source" table). The source table writes to another table with the exact same schema, minus the identity, every night (call it the "historical" table). Records older than 3 days are deleted from the source table but are never deleted from the historical table. About a week ago it occurred to me that the PK field could outnumber the max int value so I decided to switch it to a bigint (it was always an identity seed) data type on both tables.

    The issue I'm running into is that the PK field seems to be duplicating over time. Specifically, on 2011-10-28 (when the field was int) there was an ID with a value of 216754. On 2011-11-25 (after the field had been a bigint for a few days) there was another entry for ID 216754 (for a completely different record). The reason there was not a PK violation is b/c the record from 10/28 had already been deleted from the source table (older than 3 days). Lastly, the real problem is that I can't accurately populate the historical table with data from the source table since a bunch of IDs already exist in the historical table and I'm doing a query similar to this:

    INSERT INTO [Temp_Import_historical]
    SELECT * FROM [Temp_Import] WHERE NOT EXISTS (select [Temp_Import_ID] from [Temp_Import_historical] where [Temp_Import_ID] = [Temp_Import].[Temp_Import_ID

    I know this sounds really bizarre, but this is exactly what I'm running into. Could this be the result of going from int to bigint? Somehow, some way, it decided to reseed the identity field?? I have never run a truncate on the table (i know that would cause the values to be reused).

    Any thoughts are greatly appreciated.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    How did you change the datatype?
    Did you script it, or did you use the GUI wizard?
    FYI, if you truncated the table the numbering would start all over again with 1, and you'd get lots of dups.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2003
    Posts
    97
    How did you change the datatype? ---> Through management studio.

    As stated in my post, I did not truncate table.

    Any other thoughts?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    When you change the datatype of an identity column, SQL Server has to rebuild the table. During that rebuilding process, the identity seed gets reset back to zero.

    You need DBCC CHECKIDENT (Transact-SQL) to reseed the table with a new identity that is higher than the largest value you have now in your archive.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

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