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.
SELECT Name FROM FinancialInstitution WHERE Name IS NULL;
orSELECT COUNT(*) ... WHERE Name IS NULL;
COALESCE()
function) or ignore those rows (do not insert them into Person) by altering theUSING
clause in the merge.