4

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.

Community
  • 1
  • 1
My Other Me
  • 5,007
  • 6
  • 41
  • 48
  • I don't understand why you are assigning an ID if you are looking to disregard the value when a duplicate already exists. I would omit the ID column from the insert, and just get the @@IDENTITY value if it's necessary. – OMG Ponies Feb 08 '10 at 07:24
  • OMG: Who said anything about an ID? I'm talking about a key made up of the minute for which the stats are for. – My Other Me Feb 08 '10 at 07:34
  • possible duplicate of [Does SQL Server Offer Anything Like MySQL's ON DUPLICATE KEY UPDATE](http://stackoverflow.com/questions/1197733/does-sql-server-offer-anything-like-mysqls-on-duplicate-key-update) – Alex Angas Aug 10 '10 at 02:48

3 Answers3

2

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.

GSerg
  • 76,472
  • 17
  • 159
  • 346
  • 2
    Do you need a HoldLock hint on the update to ensure the lock on the index isn't released before the INSERT? – Iain Apr 21 '11 at 16:36
  • 1
    @Iain you are correct, a holdlock is needed, otherwise it's a RACE issue, where if two transactions are doing the same thing, @@rowcount will be zero and both will insert. – Alex Mar 16 '15 at 02:13
1

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.

davek
  • 22,499
  • 9
  • 75
  • 95
  • 1
    Is that atomic enough that I could run it from several threads? Should I then insert a 0 count and always call the insert and the update? – My Other Me Feb 08 '10 at 07:36
0

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)

Haim Evgi
  • 123,187
  • 45
  • 217
  • 223