Post reply

Violation of PRIMARY KEY constraint 'PK_tbl_applicant_skills_test2'. Cannot insert duplicate key in object 'dbo.tbl_applicant_skills_test2'. The duplicate key value is (112).

  • May 18, 2012 at 4:16 am

    #392285

    Dear All,

    I'm trying to insert records from multiple tables into a new table but I get the following error message: Violation of PRIMARY KEY constraint 'table name'. Cannot insert duplicate key in object Would any please let me know what I should do to resolve the error.

    Thank you in advance!

  • May 18, 2012 at 4:27 am

    #1489561

    As the message says that you are inserting the duplicate data. Try to find what is the source of duplication and remove dup record before inserting into the table.

    One way could be that you insert the data into a dummy table or temp table using select into from multiple tables. If the target table is empty.Then use following to find the dup rows

    select primary key column1(from new table) ,count(*)

    from temp table

    group by primary key column1(from new table)

    having count(*) > 1

    This will give you the records which are violating PK. Then You decide what you want to do with dup records .

    If all the tables are part of single insert statement into new table...Then you can use the above select directly and no need to create temp table.

    If target table is not empty then you could join target table with the temp table and see which are the record both in target table and temp table.

    Also, to avoid this issue you could use a merge instead of insert specially when the temp table data doesnt contain any duplicate.

    GulliMeel

    Finding top n Worst Performing queries[/url]
    Improve the performance of Merge Join(special case)
    How to Post Performance Problem -Gail Shaw[/url]

  • May 18, 2012 at 4:34 am

    #1489564

    First check that the records you are trying to insert exists or not in destination table. You'll find that record there. So you can't insert that record with currect schema.

    If you still want to insert it, you'll need to make change in your primary key. But before doing that, be very sure that you really need to insert that record

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Viewing 3 posts - 1 through 2 (of 2 total)

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