How can I avoid failing an entire INSERT batch due to 1 NULL

Question

I have a table with a primary key and a stored procedure I use to insert to that table. I have no control over the stored procedure and cannot change it. Sometimes the procedure returns many records and 1 record with a NULL value for the primary key column. At the moment, the entire batch of new rows fails to insert.

How can I configure my code or the table to fail only on the 1 row with the NULL value, but allow the other rows to be inserted?

Here is some test code:

    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

Have a read of this. http://stackoverflow.com/questions/728898/can-you-do-a-select-on-the-results-of-a-stored-procedure-in-t-sql. Far from brill though... — Tony Hopkinson, Feb 15 '12 at 22:58
What is the desired outcome if the value is null to ignore those rows or to default in a 'valid' value. — Donald, Dec 30 '20 at 14:51

score 4 · Answer 1 · answered Feb 15 '12 at 23:05

Assuming we're not really talking about a #temporary table, you could create an instead of insert trigger.

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

You may also want to use GROUP BY col1 to prevent PK violations, but that depends on whether you want to handle that gracefully or raise an error.

You could also remove the NOT NULL constraint and instead of having a PRIMARY KEY, create a unique filtered index WHERE col1 IS NOT NULL.

paparazzo · Answer 2 · 2012-02-16T23:32:55.280

0

Don't have NOT NULL on col INT. It can still be PK but it will only allow one null. Then you can delete the null row.

OK I was wrong the PK cannot be null. But you can have a null unique non clustered index. It is fundamentally a PK but not clustered. If the insert has at most one null then the insert will succeed and you can then delete the 1 row that is null.

edited Feb 16 '12 at 23:32

answered Feb 15 '12 at 23:00

paparazzo

44,497
23
105
176

What if the set is `values (null), (1), (null), (2)`? – Aaron Bertrand Feb 15 '12 at 23:05
2

SQL Server does not seem to allow nullable primary keys at all. – Joachim Isaksson Feb 15 '12 at 23:07
My suggestion was for the fallback of a unique constraint (which does allow **one** NULL). – Aaron Bertrand Feb 15 '12 at 23:12
Sorry you are right. I did test it by creating a table in SQL Management Studio and I had an nullable int as PK. Table saved without an error so assumed it took. But it did NOT save as nullable. – paparazzo Feb 16 '12 at 19:15
Did some more testing and null cannot be a PK but it can be unique non clustered. – paparazzo Feb 16 '12 at 19:46
@AaronBertrand the problem state said sometimes return 1 null. So is it could do the insert then delete the null. – paparazzo Feb 16 '12 at 19:47
PK still doesn't make sense since it can't allow any `NULL`s (whether the potential to have 1 or more than one is the same). – Aaron Bertrand Feb 16 '12 at 19:51
@AaronBertrand You criticise my answer of using unique key rather than a PK as it will accept null and then you post it in your answer. Thanks for help on my insert speed question. – paparazzo Feb 17 '12 at 00:46
@BalamBalam what are you talking about? I criticized the fact that you suggested a PK allows one null. Where in my answer do I say anything about allowing nulls in a PK column? Anyway I didn't down-vote you for it even though you were incorrect. What would you like me to do? Go back in time and read your correction before I commented on what your answer **USED** to say? People point out incorrect statements not to give you a hard time, but to prevent other users from inheriting perceptions based on those incorrect statements. We're all here to learn and help others learn, right? – Aaron Bertrand Feb 17 '12 at 00:48
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/7827/discussion-between-balambalam-and-aaron-bertrand) – paparazzo Feb 17 '12 at 00:52

How can I avoid failing an entire INSERT batch due to 1 NULL

2 Answers2