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