Detect which dataset row violates the DB unique constraint during insertion

Question

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?

Answer 1

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).

Answer 2

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