How to use different sub queries with SQL MERGE

Question

Can we pass different sub queries in SQL MERGE for inserting and updating data?

  MERGE TableA AS SOURCE
    USING TbaleB AS TARGET
    ON (SOURCE.ID=TARGET.ID) 
    WHEN NOT MATCHED BY TARGET  
    THEN INSERT(COLUMN1, COLUMN2,COLUMN3 ) 
        SELECT * FROM TableC
    WHEN MATCHED 
    THEN UPDATE COLUMN1=SOURCE.Column1, COLUMN2=SOURCE.Column2,COLUMN3=SOURCE.Column3
   FROM Table4  
   INNER JOIN INNER JOIN Table5
    ON  Table4.ID=Table5.ID
     WHERE Table5.ID=1

OUTPUT $action, inserted.*;

Is there any other option to do this?

EDIT: Using below script to create tables, insert data,

CREATE TABLE TableA([ID] [int] IDENTITY(1,1) NOT NULL,
    ColumnA [nvarchar](50) NULL,
    ColumnB [nvarchar](50) NULL,
    ColumnC [nvarchar](50) NULL,    
) ON [PRIMARY]

CREATE TABLE TableB([ID] [int] IDENTITY(1,1) NOT NULL,
    ColumnA [nvarchar](50) NULL,
    ColumnB [nvarchar](50) NULL,
    ColumnC [nvarchar](50) NULL,    
) ON [PRIMARY]

Insert into TableA values('A','1','W')
Insert into TableA values('A','2','X')
Insert into TableA values('B','1','Y')
Insert into TableB values('A','1','U')--To be update
Insert into TableB values('B','2','N')--New row to insert

Used below query to insert:

SELECT Source.* INTO #tempTable 
FROM (SELECT row_number() OVER ( PARTITION BY ColumnA,ColumnB ORDER BY ColumnA,ColumnB ) Row_ID, * --using row_number to remove duplicate rows
FROM  TableB) Source  
LEFT OUTER JOIN TableA Target 
ON  Source.ColumnA =  Target.ColumnA AND  Source.ColumnB =  Target.ColumnB 
WHERE Row_ID=1 AND Target.ColumnB IS NULL  AND Target.ColumnB IS NULL  
ALTER TABLE #tempTable drop column Row_ID, ID 

INSERT INTO TableA(ColumnA,ColumnB ,ColumnC) 
select * from #tempTable 

Used below query to update existing record:

UPDATE Target SET Target.ColumnC =Source.ColumnC
FROM TableA Target  
INNER JOIN TableB AS Source
ON  Source.ColumnA =  Target.ColumnA AND  Target.ColumnB =  SOURCE.ColumnB

And I need the output as

ID  ColumnA ColumnB ColumnC
4   A   1   U
5   A   2   X
6   B   1   Y
7   B   2   N

It is working fine, but for performance perpose, can we do the same by using MERGE?

Answer 1

Try the following:

WITH S AS
(
    SELECT
        B.ColumnA, B.ColumnB, B.ColumnC
    FROM 
    (
        SELECT 
            B.ColumnA, B.ColumnB, B.ColumnC,
            ROW_NUMBER() OVER (PARTITION BY B.ColumnA, B.ColumnB ORDER BY B.ColumnA, B.ColumnB) AS RowId
        FROM TableB B
    ) B
    WHERE
        B.RowId = 1

)
MERGE 
    TableA T
USING 
    S ON T.ColumnA = S.ColumnA AND T.ColumnB = S.ColumnB
WHEN NOT MATCHED BY TARGET 
    THEN INSERT (ColumnA, ColumnB, ColumnC)
    VALUES (S.ColumnA, S.ColumnB, S.ColumnC)
WHEN MATCHED AND
     (S.ColumnC <> T.ColumnC OR S.ColumnC IS NULL AND T.ColumnC IS NOT NULL OR S.ColumnC IS NOT NULL AND T.ColumnC IS NULL)
     THEN UPDATE SET T.ColumnC = S.ColumnC;