为什么有时在合并命令上会出现错误“尝试将非 NULL 列的值设置为 NULL"? [英] why is there the error `Attempting to set a non-NULL-able column's value to NULL` on merge command sometimes?

问题描述

我使用 merge 语句插入新数据或删除数据.我使用 temp table 作为 source 和 table 作为 target.

目标表与另一个表(Table A)有一个外键y.

在执行(when match then delete statement)时有时会遇到以下错误.

错误:试图将不可 NULL 列的值设置为 NULL.

如果我评论这一行，查询将完美运行.

如果我删除 Material table 和 table A 之间的 foreign key 查询也可以完美运行.

我已经应用了这个Hotfix，但我还提到了问题.

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

**Target Tbale(材料)** **表 A**PatientIdRef(外键) PatientId(主键)VisitNumberRef(外键) VisitNumber(主键)单价名称Unit_Price_Ins 系列…………创建表#Temp(patinetId [varchar](50) 非空，[Visit_Number] [smallint] NULL，[Unit_Price] [浮点数] NULL，[Unit_Price_Ins] [浮点数] NULL，……......)合并材质为目标使用(选择 patinetId ,Visit_Number,Unit_Price,Unit_Price_Ins来自#Temp) 作为来源on (source.patientid=target.patientid collat​​e arabic_ci_as 和 source.visit_number=target.visit_number)匹配时删除不匹配时插入(patinetIdref ,Visit_Numberref,Unit_Price,Unit_Price_Ins )值(patinetId ,Visit_Number,Unit_Price,Unit_Price_Ins)

解决方案

由于您在目标表具有多个外键(FK)约束的情况下遇到此问题，很可能是由于此错误:<当您在SQL Server 2008、SQL Server 2008 R2 或 SQL Server 2012

您正在运行 SQL Server 2008 R2 SP1 版本 10.50.2550

此问题已在 SQL Server 2008 R2 SP1 版本 10.50.2822 及更高版本(累积更新 8)中得到修复.

从此处下载:SQL Server 2008 R2 Service Pack 1 的累积更新包 8p>

或者最好下载适用于 SQL Server 2008 R2 SP1 的最新累积更新:SQL 累积更新包 13服务器 2008 R2 SP1

<小时>

您提到您已经为此应用了 SQL Server 修补程序之一，但我建议您通过执行 SELECT @@VERSION 并验证版本号是否正确安装10.50.2822 或更高版本.

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)

解决方案

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.

这篇关于为什么有时在合并命令上会出现错误“尝试将非 NULL 列的值设置为 NULL"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！