[SOLVED] Using multiple statements in WHEN MATCHED

Using multiple statements in WHEN MATCHED

I am using MERGE statement in order to insert XML input to SQL Server database table. How to execute multiple conditions in WHEN MATCHED block. Please refer the below code.

USING TableRelationship AS new
  ON (new.TableRelationshipTypeID = old.TableRelationshipTypeID) AND old.ToRoleID = @RoleID

WHEN MATCHED THEN 
   UPDATE 
       SET old.FromRoleID = new.FromRoleID
   -- Condition 2
   -- Condition 3

Currently WHEN MATCHED it only executes this old.FromRoleID = new.FromRoleID line. How can I execute all 3 lines (-- Condition 2 and 3) inside WHEN NOT MATCHED condition.

Ex :

This is what I expect. WHEN MATCHED I just want to update the old field (old.ThruDate = GETDATE()) and insert a record to the same table. I cant separate those statements by a comma. SQL emits

Incorrect Syntax

MERGE INTO Party.TableRelationship AS old
USING TableRelationship AS new ON (new.TableRelationshipTypeID = old.TableRelationshipTypeID) AND old.ToRoleID = @RoleID

WHEN MATCHED THEN 
   UPDATE 
       SET old.ThruDate = GETDATE(),
   INSERT (FromRoleID, ToRoleID, TableRelationshipTypeID)
   VALUES (new.FromRoleID, new.ToRoleID, new.TableRelationshipTypeID);

Thank you.

Solution

You could use INSERT over DML to achieve it:

INSERT INTO tab_name(FromRoleID, ToRoleID, TableRelationshipTypeID)
SELECT FromRoleID, ToRoleID, TableRelationshipTypeID
FROM (
  MERGE INTO Party.TableRelationship AS old
  USING TableRelationship AS new 
     ON new.TableRelationshipTypeID = old.TableRelationshipTypeID 
    AND old.ToRoleID = @RoleID
  WHEN MATCHED THEN 
    UPDATE SET old.ThruDate = GETDATE()
    OUTPUT $action, FromRoleID, ToRoleID, TableRelationshipTypeID
) sub(action, FromRoleID, ToRoleID, TableRelationshipTypeID)
WHERE action = 'UPDATE';

Insert the results of the MERGE statement into another table