Detect which dataset row violates the DB unique constraint during insertion

I'm inserting rows into a table from other tables in the SQL Server management Studio and 开发者_开发知识库some records violates a unique constraint because initial data is 'dirty' and is inconsistent.

How can I find which records violate?

---

Unfortunately your only solution is to validate the constrain with a query against the source data to find the culprits. If you'd use something like SSIS you could configure it to skip over the rows that violated the constraint(s).

---

Work out what the key is, and try some of the following:

1) Data already exists:

select sourcekeyfield from sourcetable where exists (select * from desttable where destkeyfield=sourcekeyfield)

2) Duplicates in source:

select sourcekeyfield, count() from sourcetable group by sourcekeyfield having count()>1

---

Check this link:

DataSet hell - "Failed to enable constraints. One or more rows contain values...."

Sanjay Sheth states there:

The solution lies in the GetErrors method of the DataTable class: http://msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemDataDataTableClassGetErrorsTopic.asp

Essentially, when I see the message above, I check the HasErrors property for each DataTable in the data set and then invoke the GetErrors method on the tables reporting errors. The GetErrors returns a collection of DataRows and you can invoke the .RowError property on the each of the error-stricken rows to find out just exactly what the problem is.

and adds:

...turn off the .EnableConstraints before the Fill. I normally enable the constraints again, only after the original Fill is complete. Then, you can catch the exception above and launch a error detection function which does what I described above.