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.

I tried WHEN NOT MATCHED THEN INSERT (KEY1,KEY2,VAL) VALUES ('datakeyA1', 'datakeyA1', 'datakeyA1'), ('datakeyB1', 'datakeyB1', 'datakeyB1');. This seems to be an invalid syntax. — smatter
– smatter, Commented Nov 25, 2014 at 11:10
I am attempting to insert multiple rows, but looking at how to do update instead of insert when matching keys are found in the existing data. (i.e the same functionality as ON DUPLICATE KEY UPDATE in MySql — smatter
– smatter, Commented Nov 25, 2014 at 11:25

GarethD · Accepted Answer · 2014-11-25 11:35:02Z

4

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);

answered Nov 25, 2014 at 11:35

GarethD

70k11 gold badges90 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jacopo Over a year ago

I was searching for a solution to update multiple rows in a single query, just like the INSERT INTO MyTable (a,b,c) VALUES (1,2,3),(3,4,5),[...] and this really does it. Thanks

Steve Ford · Accepted Answer · 2014-11-25 11:43:58Z

Try using MERGE like this:

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE tableA
(
    KEY1 VARCHAR(50),
    KEY2 VARCHAR(50),
    VAL VARCHAR(50)
    PRIMARY KEY (Key1, Key2)         
) 

INSERT INTO tableA
VALUES ('datakeyA1', 'datakeyA2', 'somevaluetoinsertorupdate')

Query 1:

MERGE  tableA AS T
USING
    (VALUES ('datakeyA1','datakeyA2','SOME NEW VALUE'), 
            ('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 T.Val = S.Val
WHEN NOT MATCHED BY TARGET
    THEN INSERT (Key1, Key2, VAL) VALUES (S.Key1, S.Key2, S.Val);

SELECT *
FROM tableA

Results:

|      KEY1 |      KEY2 |                       VAL |
|-----------|-----------|---------------------------|
| datakeyA1 | datakeyA2 |            SOME NEW VALUE |
| datakeyB1 | datakeyB2 | somevaluetoinsertorupdate |
| datakeyC1 | datakeyC2 | somevaluetoinsertorupdate |

Collectives™ on Stack Overflow

Insert or Update multiple rows in Sql Server

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related