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.

Community
  • 1
Yugo Amaryl
  • 433
  • 2
  • 6
  • 9
  • 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 08: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 08: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 08: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 08: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 09:08
  • @YugoAmaryl That would work, yes. – Mark Sinkinson Apr 11 '14 at 09:29

1 Answers1

12

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

You want:

SELECT Name FROM FinancialInstitution WHERE Name IS NULL
Mark Sinkinson
  • 10,547
  • 3
  • 46
  • 54
  • 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 08: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 07:24