SQL Server foreign key conflict in a multi values statement?

Question

I have a SQL Server INSERT sentence like below:

insert into foo( num, a, b ) values 
(1, a, b), 
(2, a, b), 
...
(9999, a, b);

But there is a "INSERT statement conflicted with the FOREIGN KEY constraint" error. The problem is that SQL Server does not tell me which exactly is the value or line with the problem. How can I quickly find what the value with the error?

On which column is the foreign key, and which is the constraint? — Davide Visentin, Sep 01 '16 at 15:01
If you want to fine the line with the problem, then split the inserts into separate statements and determine the one that fails. — Gordon Linoff, Sep 01 '16 at 15:12

score 2 · Accepted Answer · answered Sep 01 '16 at 15:06

You can turn this into a SELECT statement to find the rows that do not match.

Assuming that the column a references the table other the following will show the values that are not present in the other table:

select *
from (
  values 
   (1, a, b), 
   (2, a, b), 
    ...
   (9999, a, b)
) t(num,a,b)
where not exists (select 1 
                  from other o 
                  where o.id = t.a);

The select .. from ( values ) requires SQL Server 2012 or newer - but as you have used that tag, you should be able to use that.

. . I removed the comment. I somehow parsed it as violating a unique constraint. — Gordon Linoff, Sep 02 '16 at 01:08

SQL Server foreign key conflict in a multi values statement?

1 Answers1

Linked