SQL Server 2005, bulk UPDATE or INSERT

Question

Hello,

I'm looking for a solution to perform Insert, on duplicate key Update like operation in SQL Server 2005. This operation might Insert or Update large number of entries. SQL Server 2008 has a neat operation MERGE which would do it perfectly, the problem is we're stuck with SQL Server 2005.
I've looked into standard solutions, but all of them are not good, because they assume, that only one entry is updated/inserted.
Does anyone know a way to replicate MERGE behavior in older versions of SQL Server?

Answer 1

Alex Kuznetsov's blog contains a suggestion using the OUTPUT clause of an UPDATE statement. To paraphrase the example from that blog entry (untested):

DECLARE @updated_ids table(id int)

UPDATE table
   SET ...
OUTPUT inserted.id INTO @updated_ids
  FROM table INNER JOIN data-to-insert ON table.id = data-to-insert.id 

INSERT INTO table
SELECT ...
  FROM data-to-insert
 WHERE id NOT IN (SELECT id FROM @updated_ids)