"Cannot insert the value NULL", but there is no NULL values! 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.
NULL is not a value. Something cannot '=' NULL

You want:

SELECT Name FROM FinancialInstitution WHERE Name IS NULL