why is there the error `Attempting to set a non-NULL-able column's value to NULL` on merge command sometimes?

Question

I use merge statement for inserting new data or deleting data. I use a temp table as source and a table as target.

the target table has a foreign key with another table(Table A).

I encounter following error sometimes when (when matched then delete statement) is executed.

error:
    Attempting to set a non-NULL-able column's value to NULL.

If I comment this line the query is run perfectly.

also If i remove the foreign key between Material table and table A query is run perfectly too.

I have applied this Hotfix but I have mentioned problem yet.

SQL Server version:Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64)

**Target Tbale (Material)**                **Table A**

PatientIdRef (ForeignKey)             PatientId(Primary Key)
VisitNumberRef(Foreign Key)           VisitNumber(Primary Key)
Unit_Price                            Name
Unit_Price_Ins                        family
....
....                            

create tabl #Temp 
(
  patinetId [varchar](50) NOT NULL,
  [Visit_Number] [smallint] NULL,
  [Unit_Price] [float] NULL,
  [Unit_Price_Ins] [float] NULL,
  ......
  .....
)
merge materials as target
using(select patinetId ,Visit_Number,Unit_Price,Unit_Price_Ins
from #Temp
) as source
on (source.patientid=target.patientid collate arabic_ci_as and source.visit_number=target.visit_number)
when matched then delete
when not matched then insert
(patinetIdref ,Visit_Numberref,Unit_Price,Unit_Price_Ins )
values(patinetId ,Visit_Number,Unit_Price,Unit_Price_Ins)

Answer 1

Since you are experiencing this problem in a case where the target table has more than one foreign key (FK) constraint, it's most probably due to this bug: FIX: "Attempting to set a non-NULL-able column's value to NULL" error message when you use a MERGE statement in SQL Server 2008, in SQL Server 2008 R2 or in SQL Server 2012

You are running SQL Server 2008 R2 SP1 version 10.50.2550

This is fixed in SQL Server 2008 R2 SP1 version 10.50.2822 and later (Cumulative Update 8).

Download from here: Cumulative update package 8 for SQL Server 2008 R2 Service Pack 1

Or preferably, download the latest Cumulative Update available for SQL Server 2008 R2 SP1: Cumulative update package 13 for SQL Server 2008 R2 SP1

---

You mention that you have already applied one of the SQL Server hotfixes for this, but I suggest you double check that it is installed properly by executing SELECT @@VERSION and verifying that the version number is 10.50.2822 or later.