1 record 2 tables. Identity duplication issue RRS feed

  • Question

  • Hi,

    I have to add a file info in two different tables: the first one is the dictionary and the second one is used to store the file.
    The issue is the IDs set in the second table is not correct. Could you please let me know if you see anything wrong in the following SQL:

    -- Insert in the dicctionary
    INSERT INTO DB1.[dbo].[FilesDictionary]
    VALUES (@FileRef,
    --Get the diccionary ID
    SELECT @FileDictionaryID = SCOPE_IDENTITY() 
    -- Save the file in the second table using the dictionary ID as reference
    INSERT INTO DB2.[dbo].[FilesStore]
    SELECT FileDictionaryID
               , @FileAttachment
    FROM DB1.[dbo].[FilesDictionary]
    WHERE FileDictionaryID = @FileDictionaryID

    Issue: System is saving the info like this sometimes:

    FileDictionaryID       FileDescription
    123                         Test
    124                         Test

    DB2.[dbo].[FilesStore] -- Files are saved in another DB for storage limitation and the FileDiccionaryID is the link between both DBs
    FileDicionaryID        FileAttachment
    124                         --file string <-- this id should have been 123 instead of 124
    124                         --file string

    Should I use @@identity instead of scope_identity()? Is there any other issue? I think scope_identity()  is correct based on the theory but just to be sure.

    Thanks for your help

    Wednesday, February 18, 2015 10:49 AM

All replies

  • SCOPE_IDENTITY() is the right one.

    Could you please check for any duplicates in Identity column in FileDictionary table? Identity column will not preserve the uniqueness of the column , you may need to provide either PRIMARY or UNIQUE key constraints.

    Try the below and let us know the result.

    Select FileDictionaryID, count(1) From db1.dbo.FileDictionary Group by FileDictionary having count(1) >1

    Wednesday, February 18, 2015 10:59 AM
  • Hi Latheesh,

    Thank you very much for your quick answer and help.

    There is not any duplication issue in the FileDictionary tables as FileDictionaryID is Primary and unique as Identity field.

    Should be something I'm missing

    Wednesday, February 18, 2015 11:22 AM
  • http://blog.sqlauthority.com/2009/03/24/sql-server-2008-scope_identity-bug-with-multi-processor-parallel-plan-and-solution/
    Wednesday, February 18, 2015 11:38 AM
  • I could not see any issues with your code.

    Do you mean you will have only one FileDictionaryID in FileStore Table at any point of time? Is that not possible someone inserts manually? Why cant you have a primary or unique constraint on the FileStore Table if you expect FileDictionaryID is unique?

    Wednesday, February 18, 2015 11:38 AM
  • Hi,

    Thanks both for your replies. I really appreciate it.

    FilesDictionaryID is the primary key in the FilesDictionary table but not in the FilesStore table. So FilesDiccionaryID is always unique in the FilesDictionary table but could be duplicated in the FilesStore table (it shouldn't, based on the previous code).

    Those tables are updated by the application which is calling this SP so no one could set this manually.

    I am working with SQL Server 2008 so Jn_ds could be right but I am already using the OUTPUT when the variable @FilesDictionaryID is declared so...

    It’s hard to test this because I couldn't reproduce the issue but DB data shows this issue is happening…

    I am going to investigate about the SQL Server 2008 bug but any suggestion in the meantime is welcome :) Thanks!

    • Edited by Lain1010 Wednesday, February 18, 2015 12:14 PM
    Wednesday, February 18, 2015 12:12 PM
  • If you found our answers helpful, please mark it as an answer :) Thank you
    Wednesday, February 18, 2015 12:30 PM