Equivalent of MySQL ON DUPLICATE KEY UPDATE in Sql Server

Question

I am trying to find an equivalent of the following MySql query in Sql Server (2012)?

INSERT INTO mytable (COL_A, COL_B, COL_C, COL_D) VALUES ( 'VAL_A','VAL_B', 'VAL_C', 'VAL_D') ON DUPLICATE KEY UPDATE COL_D= VALUES(COL_D); 

Can anyone help?

PS. I have read that MERGE query has similar function, but I find the syntax of that very different.

Answer 1

You are basically looking for an Insert or Update pattern sometimes referred to as an Upsert.

I recommend this: Insert or Update pattern for Sql Server - Sam Saffron

For a procedure that will be dealing with single rows, either these transactions would work well:

Sam Saffron's First Solution (Adapted for this schema):

begin tran if exists (   select *      from mytable with (updlock,serializable)      where col_a = @val_a       and col_b = @val_b       and col_c = @val_c   )   begin     update mytable       set col_d = @val_d       where col_a = @val_a         and col_b = @val_b         and col_c = @val_c;   end else   begin     insert into mytable (col_a, col_b, col_c, col_d)       values (@val_a, @val_b, @val_c, @val_d);   end commit tran 

Sam Saffron's Second Solution (Adapted for this schema):

begin tran   update mytable with (serializable)     set col_d = @val_d       where col_a = @val_a         and col_b = @val_b         and col_c = @val_c;   if @@rowcount = 0     begin         insert into mytable (col_a, col_b, col_c, col_d)           values (@val_a, @val_b, @val_c, @val_d);      end commit tran 

---

Even with a creative use of IGNORE_DUP_KEY, you'd still be stuck having to use an insert/update block or a merge statement.

• A creative use of IGNORE_DUP_KEY - Paul White @Sql_Kiwi

update mytable   set col_d = 'val_d'   where col_a = 'val_a'     and col_b = 'val_b'     and col_c = 'val_c';  insert into mytable (col_a, col_b, col_c, col_d)   select 'val_a','val_b', 'val_c', 'val_d'   where not exists (select *      from mytable with (serializable)      where col_a = 'val_a'       and col_b = 'val_b'       and col_c = 'val_c'       ); 

---

The Merge answer provided by Spock should do what you want.

Merge isn't necessarily recommended. I use it, but I'd never admit that to @AaronBertrand.

• Use Caution with SQL Server's MERGE Statement - Aaron Bertrand

• Can I optimize this merge statement - Aaron Bertrand

• If you are using indexed views and MERGE, please read this! - Aaron Bertrand

• An Interesting MERGE Bug - Paul White

• UPSERT Race Condition With Merge

Answer 2

Try this... I've added comments to try and explain what happens where in a SQL Merge statement. Source : MSDN : Merge Statement

The Merge Statement is different to the ON DUPLICATE KEY UPDATE statement in that you can tell it what columns to use for the merge.

CREATE TABLE #mytable(COL_A VARCHAR(10), COL_B VARCHAR(10), COL_C VARCHAR(10), COL_D VARCHAR(10)) INSERT INTO #mytable VALUES('1','0.1', '0.2', '0.3'); --<These are the values we'll be updating  SELECT * FROM #mytable --< Starting values (1 row)      MERGE #mytable AS target --< This is the target we want to merge into     USING ( --< This is the source of your merge. Can me any select statement         SELECT '1' AS VAL_A,'1.1' AS VAL_B, '1.2' AS VAL_C, '1.3' AS VAL_D --<These are the values we'll use for the update. (Assuming column COL_A = '1' = Primary Key)         UNION         SELECT '2' AS VAL_A,'2.1' AS VAL_B, '2.2' AS VAL_C, '2.3' AS VAL_D) --<These values will be inserted (cause no COL_A = '2' exists)         AS source (VAL_A, VAL_B, VAL_C, VAL_D) --< Column Names of our virtual "Source" table     ON (target.COL_A = source.VAL_A) --< This is what we'll use to find a match "JOIN source on Target" using the Primary Key     WHEN MATCHED THEN --< This is what we'll do WHEN we find a match, in your example, UPDATE COL_D = VALUES(COL_D);         UPDATE SET             target.COL_B = source.VAL_B,             target.COL_C = source.VAL_C,             target.COL_D = source.VAL_D     WHEN NOT MATCHED THEN --< This is what we'll do when we didn't find a match     INSERT (COL_A, COL_B, COL_C, COL_D)     VALUES (source.VAL_A, source.VAL_B, source.VAL_C, source.VAL_D)     --OUTPUT deleted.*, $action, inserted.* --< Uncomment this if you want a summary of what was inserted on updated.     --INTO #Output  --< Uncomment this if you want the results to be stored in another table. NOTE* The table must exists     ; SELECT * FROM #mytable --< Ending values (2 row, 1 new, 1 updated) 

Hope that helps