I am using the below statement to update/imsert records. The problem is I have given the condition
SOURCE.Name IS NOT NULL
Cannot insert the value NULL into column 'Name', table 'TEST2'; column does not allow nulls. UPDATE fails.
MERGE TEST2 AS TARGET
USING TEST1 AS SOURCE
ON TARGET.ID = SOURCE.ID
AND SOURCE.Name IS NOT NULL
WHEN MATCHED THEN
UPDATE SET ID=SOURCE.ID,
State = SOURCE.State
WHEN NOT MATCHED THEN
INSERT (ID, Name, City, State)
VALUES(SOURCE.ID, SOURCE.Name, SOURCE.City, SOURCE.State);
ON clause just determines the criteria for determining what a "match" is and so what branch is taken. It is not a filter.
ON clause isn't matched then you will go to the
WHEN NOT MATCHED branch so in your case this will also apply even if the ids are matched but the source name is null.
It would be possible to add these additional predicates to the various branches. E.g.
WHEN NOT MATCHED AND SOURCE.Name IS NOT NULL
But as you do not have a branch for
WHEN NOT MATCHED BY SOURCE and both of the branches you do have will exclude NULLs you might as well use a table expression to filter these all out up front.
MERGE TEST2 AS TARGET USING (SELECT * FROM TEST1 WHERE Name IS NOT NULL) AS SOURCE ON TARGET.ID = SOURCE.ID WHEN MATCHED THEN UPDATE SET ID = SOURCE.ID, Name = SOURCE.Name, City = SOURCE.City, State = SOURCE.State WHEN NOT MATCHED THEN INSERT (ID, Name, City, State) VALUES(SOURCE.ID, SOURCE.Name, SOURCE.City, SOURCE.State);