How to determine table row causing duplicate entry error in INSERT?

Question

As I get a lot of answers trying to help me with getting around the error: I am mostly interested in a way to do error handling and to detect the table row creating an error than getting around my actual problem which I only use to illustrate what is coded in the stored procedure.

---

I am doing an SQL Insert statement in a stored procedure for entries missing in the table:

INSERT INTO dbo.t1
                      (col1, col2, col3, col4, col5)
    SELECT DISTINCT col1, col2, col3, col4, col5
    FROM         dbo.t2
    WHERE     (NOT EXISTS
                          (SELECT     col1, col2, col3, col4, col5
                            FROM      t1 AS Table_1
                            WHERE     (col1 = t2.col1) AND 
                                      (col2 = t2.col2) AND
                                      (col3 = t2.col3) AND
                                      (col4 = t2.col4) AND
                                      (col5 = t2.col5))) AND
                                       col2 = 'VALUE'

t1.col1 + t1.col2 and t1.col3 + t1.col4 have a foreign key relation to another table t3.col1 + t3.col2

The stored procedure fails to finish throwing an error message that the foreign key relation has been violated; i.e. there are some entries missing in t3:

Msg 547, Level 16, State 0 [...] INSERT statement is in conflict with FOREIGN-KEY [..]

What I would like to know is the TABLE ROW of t2 causing the error, optimally the values in this row. I googled quite a lot about SQL error handling, but only found examples providing the coding line raising the error - which is useless information for me...

Any help appreciated

Answer 1

The error is pretty clear, it seams like you are trying to insert a value in the column with the FK constraint that is not found in the primary key column of the referenced table.

From the query you posted, you are trying to insert all values from dbo.t2 that doesn't exist in the first table. Then you can use EXCEPT operator to insert only the values of col1, col2, col3, col4, col5 that found in the dbo.t2 that doesn't present in dbo.t1 something like:

INSERT INTO dbo.t1
                  (col1, col2, col3, col4, col5)
SELECT * FROM
(
    SELECT col1, col2, col3, col4, col5
    FROM         dbo.t2
    EXCEPT 
    col1, col2, col3, col4, col5
    FROM         dbo.t1
)

This way you will guarantee that only the rows that doesn't present in dbo.t1 get inserted.

If you want to get the data that cause the duplicate entry you can use the INTERSECT to get them.