如何避免由于1 NULL导致整个INSERT批处理失败

Question

我有一个带有主键的表和一个用于插入该表的存储过程。我无法控制存储过程，也无法更改它。有时存储过程会返回许多记录和一个主键列为NULL值的记录。目前，整批新行都无法插入。

如何配置我的代码或表，使其仅在具有NULL值的1行失败，但允许插入其他行？

以下是一些测试代码：

    IF OBJECT_ID('tempdb..#tbl') IS NOT NULL
     DROP TABLE #tbl
    CREATE TABLE #tbl (
     col INT NOT NULL,
     CONSTRAINT PK_tbl PRIMARY KEY (col ASC)
     WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE  = OFF,
     IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
    )
    SET XACT_ABORT OFF
    --Imagine this is the procedure that cannot be edited
    insert into #tbl (col) values 
    (null), (1), (2)
    --Ideally, the table would have 1 and 2
    select * from #tbl

Answer 1

假设我们实际上不是在谈论#临时表，那么您可以创建一个而不是插入触发器。

CREATE TRIGGER dbo.PreventNullsOnTableName
ON dbo.TableName
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT dbo.TableName SELECT col1 FROM inserted WHERE col1 IS NOT NULL;
END
GO

您可能还想使用GROUP BY col1来防止PK冲突，但这取决于您是想优雅地处理还是引发错误。

您也可以删除NOT NULL约束，而不是使用PRIMARY KEY，创建一个唯一的过滤索引WHERE col1 IS NOT NULL。

Answer 2

列INT上没有NOT NULL。它仍然可以是PK，但只允许一个NULL。然后可以删除空行。

好吧，我错了PK不能为空。但是您可以有一个null唯一的非聚集索引。它从根本上是PK，但不是集群。如果插入最多有一个null，则插入将成功，然后您可以删除为null的1行。