为什么我在硬编码值上得到“不能将值NULL插入到'X'列”错误?

INSERT INTO DDA_MatchStrings 
(Unit, FamilyID, AccountNumber, MatchType,  MatchString, SnapshotDate,  IgnoreRecord,   Merged)
SELECT 
 Unit, FamilyID, AccountNumber, 'EMAIL',    MatchString, @SnapshotDate, 0,              0
FROM DDA_MatchStrings_temp AS tms 
WHERE tms.MatchString IN 
    (SELECT tms.MatchString FROM DDA_MatchStrings_temp AS tms
    GROUP BY tms.MatchString
    HAVING COUNT(DISTINCT unit) > 1)
ORDER BY tms.MatchString

I added indentation to the field references for visual observation.

我在字段引用中添加了缩进以供视觉观察。

This statement is in a stored procedure, but when the SP gets to this point, it is giving the following error:

此语句位于存储过程中，但当SP到达此点时，它将给出以下错误:

Cannot insert the value NULL into column 'IgnoreRecord', table 'Db.dbo.TableX'; column does not allow nulls. INSERT fails.

无法将值NULL插入到列“IgnoreRecord”、表“Db.dbo.TableX”中;列不允许为空。插入失败。

I know what this error means. I'm not new to T-SQL. However, I'm completely baffled why it's giving me this error? The value isn't null. It's a hardcoded 0. FYI, the field in question is a tinyint. It should accept this value. Also, this statement is also in other stored procedures (with only a different value in the MatchType field.

我知道这个错误是什么意思。我对T-SQL并不陌生。但是，我完全搞不懂为什么它会给我这个错误?价值并不是零。这是一个硬编码0。顺便说一句，我们讨论的领域是一个很小的领域。它应该接受这个值。同样，该语句也在其他存储过程中(在MatchType字段中只有不同的值)。

What might cause this error?

什么可能导致这个错误?

UPDATE

更新

I've chosen to answer my own question, rather than delete it. This is a good case where not paying attention to db schemas has caused problems. It's worth updating as a reference that you should always pay attention to your SQL object definitions and which schemas are provided when executing code. I hate to be the one making a mistake, but hopefully others will learn from my error.

我选择回答我自己的问题，而不是删除它。这是一个很好的例子，不注意db模式会导致问题。值得更新的是，您应该始终注意SQL对象定义，以及在执行代码时提供哪些模式。我不喜欢犯错，但希望别人能从我的错误中吸取教训。

1 个解决方案

#1

Ok... I feel a bit stupid but I might as well answer this for posterity.

好吧……我觉得自己有点傻，但我不妨为子孙后代回答这个问题。

The issue is not with the insert statement, or my script... it relates to schemas. The definition of the SP is:

问题不在于插入语句，也不在于我的脚本……它与模式。SP的定义是:

CREATE PROCEDURE dbo.DDA_Generate_PotentialDuplicate_Emails

Apparently, at some point I defined it as:

显然，在某一时刻我将它定义为:

CREATE PROCEDURE DDA_Generate_PotentialDuplicate_Emails

This is the first time where I've worked with a corporate db where, by default, the associated schema was the user's login and not dbo.

这是我第一次使用企业数据库，默认情况下，关联的模式是用户的登录，而不是dbo。

So... when I changed my execution code from:

所以…当我将执行代码从:

EXEC DDA_Generate_PotentialDuplicate_Emails

来

EXEC dbo.DDA_Generate_PotentialDuplicate_Emails

Everything started to work properly because I was running the proper version of the stored procedure. :P

一切都开始正常工作，因为我正在运行正确的存储过程版本。:P

#1