Getting Cannot insert the value NULL into column message

If you are getting "SQL error description: [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert the value NULL into column 'xyz', table 'tempdb.dbo.t_longId'; column does not allow nulls. INSERT fails." you can look to see if you are in the following scenario and understand what you should change.
I got the error while doing a hands-on with an insert_recordset. Normally, after thinking that I do not have NULL values in my table, I realized I am also using a view in the select. I opened the view in AX, but of course you are not seeing NULLs, just empty strings (forgot to mentioned - the column it was complaining about was a string column). Jumped to SSMS and selecting from the view I started to see the NULL values. Looked at the view's definition and ... of course, I am using an Outer Join between some tables.

The issue: if you are creating a query, in which you have (let's say only) two tables, with an outer join between them. This of course means that there might be resulting records that are having NULL values in case you have records in the first table that do not have matching records in the second table based on the relation.

So, from here the problem: when you will try to use the values for inserting you will get into that error, depending on the data you have (a note here: be very careful since this might not always fail - if you don't have parents with missing childs, but with the 'right' data it will).

The fix: if there are only couple fields from the child table, maybe do a computed column and check for NULLs; if not, try to see if you can change the join to inner (unlikely, but can be an options); the final one being that you are going to split your insert into an insert and an update.

No comments:

Post a Comment