SQL Merge Statement Check Constraint Error

Question

I have the following code table A has a check constraint on column Denial.

CREATE TABLE Table a
(
    [ID] int IDENTITY(1,1) NOT NULL  ,
    [EntityID] int ,
    Denial nVarchar(20) 

    CONSTRAINT Chk_Denial CHECK (Denial IN ('Y', 'N')),
)

Merge statement

MERGE INTO Table a WITH (HOLDLOCK) AS tgt
USING (SELECT DISTINCT 
           JSON_VALUE(DocumentJSON, '$.EntityID') AS EntityID,
           JSON_VALUE(DocumentJSON, '$.Denial') AS Denial
       FROM Table1 bd
       INNER JOIN table2 bf ON bf.FileUID = bd.FileUID
       WHERE bf.Type = 'Payment') AS src ON tgt.[ID] = src.[ID]  

WHEN MATCHED 
))  THEN 
        UPDATE SET tgt.ID = src.ID,
                   tgt.EntityID = src.EntityID,
                   tgt.Denial = src.Denial,
            
WHEN NOT MATCHED BY TARGET
    THEN INSERT (ID, EntityID, Denial)
         VALUES (src.ID, src.EntityID, src.Denial)
    
THEN DELETE

I get this error when running my MERGE statement:

Error Message Msg 547, Level 16, State 0, Procedure storproctest1, Line 40 [Batch Start Line 0]
The MERGE statement conflicted with the CHECK constraint "Chk_Column". The conflict occurred in the database "Test", table "Table1", and column 'Denial'. The statement has been terminated.

This is due to the source files having "Yes" and "No" instead of 'Y' and 'N'. Hence, I'm getting the above error.

How can I use a Case statement in merge statement to handle the above Check constraints error? or Any alternative solutions.

Answer 1

You can turn Yes to Y and No to N before merging your data. That would belong to the using clause of the merge query:

USING (
    SELECT Distinct 
        JSON_VALUE(DocumentJSON, '$.EntityID') AS EntityID,
        CASE JSON_VALUE(DocumentJSON, '$.Denial') 
            WHEN 'Yes' THEN 'Y'
            WHEN 'No'  THEN 'N'
            ELSE JSON_VALUE(DocumentJSON, '$.Denial')
        END AS Denial             
    FROM Table1 bd
    INNER JOIN table2 bf ON bf.FileUID = bd.FileUID
    WHERE bf.Type = 'Payment' 
) AS src

The case expression translates Y and N values, and leaves other values untouched. Since this applies to the source dataset, the whole rest of the query benefits (ie both the update and insert branches).