Duplicate records

  • Hi,

    I'm inserting multiple records into a table that has a primary key with a constraint. On my insert I'm getting a 'Violation of Primary Key Constraint' error message. If there anyway I can determine what record it is complaining about when I'm doing the insert so I can further investigate?

    Thanks

  • That depends on how you are inserting them. Can you show us the code?

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • I added an insert at the beginning of this select statement to see if I could find out what the problem was and ran it from SQL Server Management Studio. The code below the insert is what I'm using to insert records using SSIS which gives me the same error.

    INSERT INTO [PSS_STAGING].[dbo].[party]

    SELECT DISTINCT

    T1.PARTY_ID

    ,PARTY_TYP_ID =

    CASE

    WHEN PARTY_TYPE = 'I' THEN 1

    WHEN PARTY_TYPE = 'C' THEN 2

    END

    ,GETDATE() AS CREATED_DTM

    ,'Migration' AS LST_UPDT_USERID

    ,GETDATE() AS LST_UPDT_DTM

    ,'N' AS IS_DELETED_IND

    FROM PARTY AS T1

    INNER JOIN PARTY_DTL AS T2 ON T1.CLIENT_ID = T2.CLIENT_ID

    ORDER BY T1.PARTY_ID

  • You would need an additional query to find the duplicate Primary Keys, or you will need a WHERE clause to pre-filter the duplicates out. If you can tell us what the target table's PK is, we can show you how to do that.

    Note that the DISTINCT does not completely work here, because you have more columns that just the primary key columns in your SELECT (I assume).

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Thanks. Yes the DISTINCT shouldn't be needed here because there should be no duplicates returned back in the results. The primary Key is Party_Id. That is where the duplicates are. I just wish SQL Server Management Studio would display the record in question when it comes back with the duplicate error message.

  • Here are the queries to find the dupes:

    --Find duplicates between source and target

    Select T1.PARTY_ID, T1.Client_ID

    From PARTY AS T1

    Inner Join [PSS_STAGING].[dbo].[party] O1

    ON O1.Party_ID = T1.Party_ID

    --Find duplicates in source:

    Select T1.PARTY_ID, Min(T1.Client_ID), Max(T1.Client_ID), count(*)

    From PARTY AS T1

    INNER JOIN PARTY_DTL AS T2 ON T1.CLIENT_ID = T2.CLIENT_ID

    Group By T1.Party_ID

    Having count(*) > 1

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • dale_keller (9/30/2008)[hrI just wish SQL Server Management Studio would display the record in question when it comes back with the duplicate error message.

    Yeah, but what if there were a million rows in conflict? What should it do with them in that case?

    The problem is that there is no way to return a dataset in an error message. And you don't want to just return it in the normal dataset return context, because that could be mistaken for valid SELECT data by an app using the query. What we need is explicit syntax to direct an error dataset to something that can hold it.

    On the other hand, now that we have multiple output stream facilities like the OUTPUT clause and the MERGE statement, maybe we will get something like this in the future.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

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

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