SQL Server 2000 equivalent for ON DUPLICATE KEY?

Question

Is there a SQL Server 2000 functional equivalent to MySQL's ON DUPLICAT KEY clause? This Answer led me to look at the Instead of Trigger construct, but the sample looks clunky, and the functionality would be hidden in the trigger, and not be directly in the stored proc.

FWIW: I am trying to create a stats table for per-minute summaries, so that I can just call one Procedure to log the fact that an action happened and increment the counter for the appropriate minute.

Answer 1

upsert

http://en.wikipedia.org/wiki/Upsert

INSERT ... WHERE NOT EXISTS (SELECT 1 FROM table WHERE id = ?)

merge

but i think is only in SQL 2003

MERGE INTO table_name USING table_name ON (condition)
  WHEN MATCHED THEN
  UPDATE SET column1 = value1 [, column2 = value2 ...]
  WHEN NOT MATCHED THEN
  INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...])

http://en.wikipedia.org/wiki/Merge_(SQL)

Answer 2

Also clunky, but you could do this in your procedure:

insert ... where not exists (...);

update ....;

knowing that your insert will fire when the data is new, and your update only when the data exists.

Answer 3

I use this:

begin tran;

update ... where ... ;

if @@rowcount = 0
  insert ...
commit tran;

As I understand it, with proper indices in place, the update places proper range locks that would prevent others from inserting same thing concurrently.