lundi 13 juin 2016

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

Aucun commentaire:

Enregistrer un commentaire