Does SQL Server Offer Anything Like MySQL's ON

问题:

In MySQL, if you specify ON DUPLICATE KEY UPDATE and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed. For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have identical effect:

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;

I don\'t believe I\'ve come across anything of the like in T-SQL. Does SQL Server offer anything comparable to MySQL\'s ON DUPLICATE KEY UPDATE?

回答1:

There\'s no DUPLICATE KEY UPDATE equivalent, but MERGE and WHEN MATCHED might work for you

Inserting, Updating, and Deleting Data by Using MERGE

回答2:

I was surprised that none of the answers on this page contained an example of an actual query, so here you go:

A more complex example of inserting data and then handling duplicate

MERGE
INTO MyBigDB.dbo.METER_DATA WITH (HOLDLOCK) AS target
USING (SELECT
    77748 AS rtu_id
   ,\'12B096876\' AS meter_id
   ,56112 AS meter_reading
   ,\'20150602 00:20:11\' AS local_time) AS source
(rtu_id, meter_id, meter_reading, time_local)
ON (target.rtu_id = source.rtu_id
  AND target.time_local = source.time_local)
WHEN MATCHED
  THEN UPDATE
      SET meter_id = \'12B096876\'
         ,meter_reading = 56112
WHEN NOT MATCHED
  THEN INSERT (rtu_id, meter_id, meter_reading, time_local)
      VALUES (77748, \'12B096876\', 56112, \'20150602 00:20:11\');

回答3:

SQL Server 2008 has this feature, as part of TSQL.
See documentation on MERGE statement here - http://msdn.microsoft.com/en-us/library/bb510625.aspx

回答4:

SQL server 2000 onwards has a concept of instead of triggers, which can accomplish the wanted functionality - although there will be a nasty trigger hiding behind the scenes.

Check the section \"Insert or update?\"

http://msdn.microsoft.com/en-us/library/aa224818(SQL.80).aspx