Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

closed as off-topic by Max Vernon, Jon Seigel, Marian, Phil, gbn Apr 30 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 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 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 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 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 at 9:08

1 Answer 1

up vote 12 down vote accepted

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

You want:

SELECT Name FROM FinancialInstitution WHERE Name IS NULL
share|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 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 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 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 at 7:24

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