MERGE TVP + sum the column while updating a record

Question

Source Table

Id, Name, hits
1   A     10    
1   A     20
1   A     30
2   A     10

Target Table

Id, Name, hits
1   A     NULL

After Merge

Id, Name, hits
1   A     60
2   A     10

is the above possible ? using Merge statement ?

Answer 1

Try below

MERGE     targetTable AS [pi]
USING     ( 
               SELECT id,name,sum(hits) as hits from sourcetable
               GROUP BY id,name
          ) AS src (id,name,hits) ON src.id= [pi].id and scr.name=pi.name
WHEN      MATCHED 
               THEN UPDATE SET [pi].hits= src.hits
WHEN      NOT MATCHED 
               THEN INSERT values (src.id, src.name,hits)