Using Merge In SQL Server To Update A Third Table

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 inse

Solution 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/

Solution 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.