Using merge in SQL Server to update a third table

Question

I've two tables A and B. Table A is the source and B is the target. Based on some conditions, I'll be either updating existing rows (selective columns only) in the B from A or inserting as a new row in B from A. A and B have the same columns. This I can do using MERGE.

Now, If the row in the table B is updated, I need to update some records in 3rd table C. Is it possible to do this in the following statement,

MERGE B
USING A
ON A.somecolumn = B.somecolumn

WHEN MATCHED THEN

UPDATE 
SET
B.somecolumn1 = A.somecolumn1,
B.somecolumn2 = A.somecolumn2

WHEN NOT MATCHED BY TARGET THEN
INSERT INTO B
somecolumn1, 
somecolumn2, 
somecolumn3,
somecolumn4 

VALUES
(
A.somecolumn1, 
A.somecolumn2, 
A.somecolumn3,
A.somecolumn4 
);

Answer 1

You need temp storage of the output from the merge statement and a update statement that use the temp table / table variable.

-- Your table A, B and C
declare @A table(ID int, Col int)
declare @B table(ID int, Col int)
declare @C table(ID int, Col int)

-- Sample data
insert into @A values (1, 1),(2, 2)
insert into @B values (1, 0)
insert into @C values (1, 0),(2, 0)

-- Table var to store ouput from merge
declare @T table(ID int, Col int, Act varchar(10))

-- Merge A -> B
merge @B as B
using @A as A
on A.ID = B.ID
when not matched then insert (ID, Col) values(A.ID, A.Col)
when matched then update set Col = A.Col
output inserted.ID, inserted.Col, $action into @T;

-- Update C with rows that where updated by merge    
update C set
  Col = T.Col
from @C as C
  inner join @T as T
    on C.ID = T.ID and
       T.Act = 'UPDATE'

https://data.stackexchange.com/stackoverflow/qt/119724/

Answer 2

This Microsoft article shows an example of using OUTPUT $action... at the end of your query populate a temp table. This is the approach you will have to use.

My previous idea of being able to use a MERGE statement as a sub query of an UPDATE on a third table does not work. I could update my answer to use a temp table/variable but the answer (and example) from @Mikael Eriksson already provide you with a clean solution, and mine would just mimic that when I was done.