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 ke
y 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)
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With