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

Answer 3

You can simulate a near identitical behaviour using an INSTEAD OF TRIGGER:

CREATE TRIGGER tMyTable ON MyTable
INSTEAD OF INSERT
AS
    BEGIN
        SET NOCOUNT ON;

        SELECT i.COL_A, i.COL_B, i.COL_C, i.COL_D, 
            CASE WHEN mt.COL_D IS NULL THEN 0 ELSE 1 END AS KeyExists 
            INTO #tmpMyTable
            FROM INSERTED i
            LEFT JOIN MyTable mt
            ON i.COL_D = mt.COL_D;

        INSERT INTO MyTable(COL_A, COL_B, COL_C, COL_D)
            SELECT COL_A, COL_B, COL_C, COL_D
                FROM #tmpMyTable
                WHERE KeyExists = 0;

        UPDATE mt
            SET mt.COL_A = t.COL_A, mt.COL_B = t.COL_B, mt.COL_C = t.COL_C
            FROM MyTable mt 
                INNER JOIN #tmpMyTable t 
                ON mt.COL_D = t.COL_D AND t.KeyExists = 1;
    END;

SqlFiddle here

How it works

• We first project a list of all rows being attempted to be inserted into the table into a #temp table, noting which of those ARE already in the underlying table via a LEFT OUTER JOIN on the key column(s) COL_D which detect the duplication criteria.
• We then need to repeat the actual work of an INSERT statement, by inserting those rows which are not already in the table (because of the INSTEAD OF, we have removed the responsibility of insertion from the engine and need to do this ourselves).
• Finally, we update all non-key columns in the matched rows with the newly 'inserted' data.

Salient Points

• It works under the covers, i.e. any insert into the table while the trigger is enabled will be subject to the trigger (e.g. Application ORM, other stored procedures etc). The caller will generally be UNAWARE that the INSTEAD OF trigger is in place.
• There must be a key of sorts to detect the duplicate criterion (natural or surrogate). I've assumed COL_D in this case, but it could be a composite key. (Key but cannot be IDENTITY for obvious reasons, since the client wouldn't be inserting an Identity)
• The trigger works for both single and multiple row INSERTS

NB

• The standard disclaimers with triggers apply, and more so with INSTEAD OF triggers - as this can cause surprising changes in observable behaviour of Sql Server, such as this - even well intended INSTEAD OF triggers can cause hours of wasted effort and frustration for developers and DBA's who are not aware of their presence on your table.
• This will affect ALL inserts into the table. Not just yours.

Answer 4

Stored Procedure will save the day.

Here I assume that COL_A and COL_B are unique columns and are type of INT NB! Don't have sql-server instance ATM so cannot guarantee correctness of the syntax. UPDATE! Here is a link to SQLFIDDLE

 CREATE TABLE mytable
(
COL_A int UNIQUE,
COL_B int UNIQUE,
COL_C int,
COL_D int,
)

GO

INSERT INTO mytable (COL_A, COL_B, COL_C, COL_D)
VALUES (1,1,1,1),
(2,2,2,2),
(3,3,3,3),
(4,4,4,4);
GO

CREATE PROCEDURE updateDuplicate(@COL_A INT, @COL_B INT, @COL_C INT, @COL_D INT)
AS
BEGIN
    DECLARE @ret INT
    SELECT @ret = COUNT(*) 
    FROM mytable p 
    WHERE p.COL_A = @COL_A 
        AND p.COL_B = @COL_B

     IF (@ret = 0) 
        INSERT INTO mytable (COL_A, COL_B, COL_C, COL_D)
        VALUES ( @COL_A, @COL_B, @COL_C, @COL_D)

     IF (@ret > 0)
        UPDATE mytable SET COL_D = @COL_D WHERE col_A = @COL_A AND COL_B = @COL_B  
END;
GO

Then call this procedure with needed values instead of Update statement

exec updateDuplicate 1, 1, 1, 2
GO
SELECT * from mytable
GO

Answer 5

There's no DUPLICATE KEY UPDATE equivalent in sql server,but you can use merged and when matched of sql server to get this done ,have a look here: multiple operations using merge