Newbie Newbie - 5 months ago 8
SQL Question

MERGE is inserting NULL records also

I am using the below statement to update/imsert records. The problem is I have given the condition

SOURCE.Name IS NOT NULL
, but still I get an error on the insert statement saying:


Cannot insert the value NULL into column 'Name', table 'TEST2'; column does not allow nulls. UPDATE fails.


And that's because there is a
NOT NULL
constraint on the Name column in the TARGET table. I just don't want to insert any records that contains NULL as Name. Plus I also don't want to update any row in the TAGRET table where Name is NULL.

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,
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);

Answer

The ON clause just determines the criteria for determining what a "match" is and so what branch is taken. It is not a filter.

If the 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);