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

closed as off-topic by Max Vernon, Jon Seigel, Marian, Philᵀᴹ, gbn Apr 30 '14 at 7:08

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Max Vernon, Jon Seigel, Marian, Philᵀᴹ
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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 '14 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 '14 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 '14 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 '14 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 '14 at 9:08
12

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

You want:

SELECT Name FROM FinancialInstitution WHERE Name IS NULL
|improve this answer
  • 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 '14 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 '14 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 '14 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 '14 at 7:24

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