How to write a check to avoid the message "INSERT statement conflicted with the FOREIGN KEY constraint"?

Question

I read and understood the entries in following asked question: INSERT statement conflicted with the FOREIGN KEY constraint . I do get the point, however, I'm in this situation where I have around 1 Gb of records need to be inserted into a table, some of those records have conflicted foreign key. The query looks like this:

IF NOT EXISTS (SELECT * FROM [dbo].[tbl_R_TaskHistory] WHERE [TaskID] = 
10000529)
BEGIN insert into [dbo].[tbl_History] 
([TaskID],[UserID],[ActD],[RequestD],[No],[SignID],[Completed]) 
values (10000529,'A0000187',NULL,5738366,0,NULL,CAST(N'2011-03-16 
04:53:37.210' AS DateTime)) END

The conflict ocurs on RequestID, so I was thinking there must be a way to make a check to avoid the error messages. My point is that I want my query to check if the RequestID has not FOREIGN KEY constraint it will not insert this record and move to the next one.

Try to use `TRY...CATCH` statement - https://learn.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql. Maybe it suits you. — Sergey Menshov, Mar 01 '18 at 07:38

score 3 · Accepted Answer · answered Mar 01 '18 at 07:38

If your query contains only one row, you can just expand the check like this:

IF NOT EXISTS (SELECT * FROM [dbo].[tbl_R_TaskHistory] WHERE [TaskID] = 10000529) AND EXISTS(SELECT 1 FROM [dbo].[...referencing table...] WHERE [RequestD] = 5738366)
BEGIN 
    insert into [dbo].[tbl_History] ([TaskID],[UserID],[ActD],[RequestD],[No],[SignID],[Completed]) 
    values (10000529,'A0000187',NULL,5738366,0,NULL,CAST(N'2011-03-16 04:53:37.210' AS DateTime));
END

Anyway, if you are inserting many rows at the same time and for performance considerations, it will be better to store the values in buffer table. Something like this:

insert into #tbl_History ([TaskID],[UserID],[ActD],[RequestD],[No],[SignID],[Completed]) 
values (10000529,'A0000187',NULL,5738366,0,NULL,CAST(N'2011-03-16 04:53:37.210' AS DateTime)) 
      ,(...)
      ,(...)
      ,(...)

Then, just perform an inner join to your referencing table:

insert into [dbo].[tbl_History] ([TaskID],[UserID],[ActD],[RequestD],[No],[SignID],[Completed]) 
SELECT [TaskID],[UserID],[ActD],[RequestD],[No],[SignID],[Completed]
FROM #tbl_History A
INNER JOIN [dbo].[...referencing table...] B
    ON A.[RequestD] = B.[RequestD];

score 0 · Answer 2 · answered Mar 01 '18 at 13:54

0

This syntax also works

declare @a int = 5;
declare @b int = 18;

insert into sample (a, b) 
select @a, @b  
where not exists (select 1 from sample where b = @b)
  and     exists (select 1 from student where iden = @a)

This avoids creating a #temp

insert into sample (a, b) 
select a, b 
from ( values (5,19)
            , (5,30)
            , (5,31)
            , (5,32)
            , (7,41)
            , (7,42)
     ) v(a,b)  
where not exists (select 1 from sample where b = v.b)
  and     exists (select 1 from student where iden = v.a)

answered Mar 01 '18 at 13:54

paparazzo

44,497
23
105
176

Not sure I follow your example. It would be helpful to explain what @a and @b and how I can relate to it in my query. I'm for sure interested in skipping the temp table. – dolly_do Mar 02 '18 at 09:45
They a variables. I suggest https://technet.microsoft.com/en-us/library/ms187953(v=sql.105).aspx – paparazzo Mar 02 '18 at 09:52

How to write a check to avoid the message "INSERT statement conflicted with the FOREIGN KEY constraint"?

2 Answers2