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.