Insert or Update multiple rows in Sql Server

Question

I need to execute a query as shown below. Columns KEY1 and KEY2 are keys that cannot be repeated. If there are keys matching, I need to perform an update VAL instead of insert. How can I do that in Sql Server?

INSERT INTO tableA
  ( 
      KEY1,
      KEY2,
      VAL,                      
  ) VALUES (
          -- Row A
          'datakeyA1',
          'datakeyA2',
          'somevaluetoinsertorupdate'
      ) , (
          -- Row B
          'datakeyB1',
          'datakeyB2',
          'somevaluetoinsertorupdate'
      ) , (
          -- Row C
          'datakeyC1',
          'datakeyC2',
          'somevaluetoinsertorupdate'
      );

I tried using MERGE, but looking at the syntax, I am not sure if it supports updating / inserting multiple rows. If anyone has encountered a similar situation in the past, could you please help out?

EDIT:

If I were using MySql, I would have just used:

ON DUPLICATE KEY UPDATE 
 VAL = VALUES(VAL)

in the query.

Answer 1

You can use a table value constructor as the source table to make this work with MERGE:

MERGE tableA AS t
USING (VALUES 
        ('datakeyA1', 'datakeyA2', 'somevaluetoinsertorupdate'), 
        ('datakeyB1', 'datakeyB2', 'somevaluetoinsertorupdate'),
        ('datakeyC1', 'datakeyC2', 'somevaluetoinsertorupdate')
    ) AS s (Key1, Key2, Val)
        ON s.Key1 = t.Key1
        AND s.Key2 = t.Key2
WHEN MATCHED THEN 
    UPDATE 
    SET    Val = s.Val
WHEN NOT MATCHED THEN 
    INSERT (Key1, Key2, Val)
    VALUES (s.Key1, s.Key2, s.Val);