6

This question follows the answer of my other question there: Insert with OUTPUT correlated to sub query table

The Person.LastName column has a NOT NULL constraint. When I execute this code:

CREATE TABLE tempIDs
( PersonId INT, 
  FinancialInstitutionId INT
);

MERGE INTO Person 
USING FinancialInstitution AS fi
  ON 1 = 0
WHEN NOT MATCHED THEN
  INSERT (CreationDate, AdministrativeStatus, LastName, Street1, Number1, City1, State1, PostCode1, CountryId1, WorkDirectPhone1, Fax1, Email1)
  VALUES (GetDate(), 'Legal', fi.Name, fi.Street, fi.Number, fi.City, fi.[State], fi.PostCode, fi.CountryId, fi.PhoneNumber, fi.Fax, fi.Email)
OUTPUT inserted.Id, fi.Id INTO tempIDs;

UPDATE fi
SET fi.PersonId = t.PersonId
FROM FinancialInstitution AS fi
  JOIN tempIDs AS t
    ON fi.Id = t.FinancialInstitutionId ;

I get the following error:

Cannot insert the value NULL into column 'LastName', table 'Person'; column does not allow nulls. UPDATE fails.

The thing is that there is no FinancialInstitution.Name that is NULL.

SELECT Name FROM FinancialInstitution WHERE Name = NULL

This returns no row. Beside, if I replace fi.Name with a value ('A Last Name') the request works.

Improve this question
6
  • It seems to me that FinancialInstitution has no rows at all. After the creation of the table, have you performed any inserts?
    – ddaniel
    Apr 11, 2014 at 8:46
  • Try this to check for nulls: SELECT Name FROM FinancialInstitution WHERE Name IS NULL; or SELECT COUNT(*) ... WHERE Name IS NULL;
    – ypercubeᵀᴹ
    Apr 11, 2014 at 8:49
  • 2
    Indeed, there are NULL values. Mark Sinkinson is right, and I still see my teacher explaining that a few years ago. I needed my memory refreshed. Sorry I bothered you folks. I'll use the COALESCE. Maybe I should delete this thread?
    – Yugo Amaryl
    Apr 11, 2014 at 8:52
  • Depending on what you want to do with these rows (that have NULL in the Name), you can either amend the value (with COALESCE() function) or ignore those rows (do not insert them into Person) by altering the USING clause in the merge.
    – ypercubeᵀᴹ
    Apr 11, 2014 at 8:55
  • Here I need to amend the value. But if I needed to ignore those rows, I guess you are thinking of simply doing this: USING (SELECT * FROM FinancialInstitution WHERE Name IS NOT NULL) AS fi
    – Yugo Amaryl
    Apr 11, 2014 at 9:08

1 Answer 1

Reset to default
12

NULL is not a value. Something cannot '=' NULL

You want:

SELECT Name FROM FinancialInstitution WHERE Name IS NULL
Improve this answer
4
  • 5
    Sh** Shame on me! Now I remember my teacher explaining that a few years ago! And that's where COALESCE become useful!
    – Yugo Amaryl
    Apr 11, 2014 at 8:47
  • Or better yet ISNULL, as when you are not looking for the first non-null value in a sequence this will be faster. COALESCE is treated as a CASE statement under the covers, I believe.
    – Steve Pettifer
    Apr 11, 2014 at 13:13
  • @StevePettifer In this case, he will need a function to simply replace nulls with some non-null value, during an INSERT statement, so there would be no difference in performance.
    – ypercubeᵀᴹ
    Apr 11, 2014 at 16:25
  • @ypercube - Not necessarily true, although the difference seems to be about 10-12% and ISNULL also seems to be less CPU intensive (see here), but it is also to case that ISNULL is not, strictly speaking, standards compliant being as it is a T-SQL specific implementation, whereas COALESCE is part of the SQL standard. Horses for courses though. I prefer the neater syntax of ISNULL where you are only testing one value and have no need to support other platforms.
    – Steve Pettifer
    Apr 14, 2014 at 7:24

Not the answer you're looking for? Browse other questions tagged or ask your own question.