Primary key violation. Cannot insert duplicate key

  • Hi

    I have been trying to deal with a bug in a stored procedure which gives the error of primary key violation.

    INSERT INTO TableA(NumberA, NumberCodeID, RouteID, DateUpdated, PlatformID, Grade, Notes)

    SELECT NumberA, @NumberCodeID, null, null, @PlatformID, -1, null

    FROM #NewNumbers a

    WHERE NOT EXISTS (SELECT TOP 1 1 FROM dbo.TableA WITH(NOLOCK) WHERE TableA.NumberA = a.NumberA AND NumberCodeID = @NumberCodeID)

    I am already checking if the row exists, and only insert the row if it does not exists. However, it still throws a primary key violation which is (NumberA, NumberCodeID) for TableA.

    Also, even if there were duplicates in the temp #NewNumbers table, I believe this query would have still taken care of the duplicates.

    Any clues as to where to look ?

  • You probably have duplicates in the SELECT statement.

    As you have not given us the PK, the best I could suggest is to try SELECT DISTINCT...

  • Even if there are duplicates in the select, will it not get caught in the if exists for the second insertion/ duplicate insertion ??

    The Primary key for TableA is mentioned in the above, but I am putting it here as well

    (NumberA, NumberCodeID)

  • namrata.dhanawade-1143388 (10/19/2009)


    Even if there are duplicates in the select, will it not get caught in the if exists for the second insertion/ duplicate insertion ??

    No - the exists checks that the PK is not already in the table. You need to ensure that any new PKs do not occur more than once in the SELECT statement.

    -- *** Test Data ***

    CREATE TABLE #temp

    (

    PK int NOT NULL PRIMARY KEY

    )

    INSERT INTO #temp

    SELECT 1

    UNION ALL SELECT 2

    UNION ALL SELECT 3

    CREATE TABLE #New

    (

    New int NOT NULL

    )

    INSERT INTO #New

    SELECT 2

    UNION ALL SELECT 3

    UNION ALL SELECT 4

    UNION ALL SELECT 4

    UNION ALL SELECT 5

    -- *** End Test Data ***

    -- This will fail as 2 and 3 are already in #temp

    INSERT INTO #temp

    SELECT New

    FROM #New

    -- This will fail as 4 occurs twice

    INSERT INTO #temp

    SELECT New

    FROM #New N

    WHERE NOT EXISTS

    (

    SELECT *

    FROM #temp T

    WHERE T.PK = N.New

    )

    -- This will work

    INSERT INTO #temp

    SELECT DISTINCT New

    FROM #New N

    WHERE NOT EXISTS

    (

    SELECT *

    FROM #temp T

    WHERE T.PK = N.New

    )

  • namrata.dhanawade-1143388 (10/19/2009)


    Even if there are duplicates in the select, will it not get caught in the if exists for the second insertion/ duplicate insertion ??

    Insert ... select does not work row by row. It does not run the select, take the first row, insert it, run the select, take the second row, insert it,....

    Insert... select works by first getting the entire resultset returned by the select and then inserting all of those rows in a single operation. By the time the insert starts, the select (with it's associated EXISTS) has been evaluated and won't be evaluated again.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks to both of you. This has really helped.

    I think, the question really was whether bulk inserts happen one row at a time. And since the answer is NO, the distinct will probably solve the problem.

    🙂

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

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