Violation of UNIQUE KEY Contstraints - Cannot insert duplicate key

Question

I am pulling out my hair over here. I have a table that has a UNIQUE Key (IX_tblTable) and the unique key is on a column Number. I am parsing some data from the web and storing it in the table. My latest collection of data from the web contains number THAT ARE NOT CONTAINED IN THE DATABASE. so I am getting data from the site and all of the data I am getting in unique, there are no duplicates and the numbers in the list that is returned are not in the database.

I keep getting this error everytime I try to update the database, what is the best way to trap the error to see which number is throwing the error. I am storing everything that comes back in an object list and when it is done running I have 131 records that need to be inserted and I can not see which one is throwing this error. What is the bast way to trap it?

EDIT: I am using sql server 2005, wirtting in C# and using Linq2SQL. I can not post any c# code at this time for proprietary reasons...

Answer 1

Create a copy of the table without a primary key or uniqueness constraint (having the column, just not as a primary). Modify your code to insert into that table. Run it. Select values having more than one duplicate.

Answer 2

Can you just disable your constraint for a while and see what duplicates save? Later you can remove duplicates and re-enable it.

Answer 3

You can use an algorithm called binary search to find the 'wrong' data.

Suppose you have 131 lines of INSERT INTO ... VALUES(...) and you want to catch the one that's causing the error, you can divide your 131 lines into two pices(first 66 and last 65). Now run that 66 and 65 INSERTs separatelly, and see which throws the error. Continue to 'divide an try' until you get to one single line. (That's 10 tries in the worst case).

Answer 4

Are you controlling the lifecycle of your datacontext?

Insert 5
SubmitChanges (record inserted, no exception)
Insert 5
SubmitChanges (duplicateKeyException on 5)
Insert 6
SubmitChanges (duplicateKeyException on 5)

Answer 5

Why not use Begin Try... End Try.. Begin Catch... End Catch... in the SQL store procedure (I assume you use the SP to insert data) to capture the Row that causes the unique constraint violation?