SQL Server 2005 Volume UPDATE or INSERT

Question

I am looking for a solution to perform insertion in a duplicate key, for example, as an operation in SQL Server 2005. This operation can insert or update a large number of records. SQL Server 2008 has a neat MERGE operation that will do it perfectly, the problem is that we are stuck with SQL Server 2005.
I looked at standard solutions, but all of them are not very good, because they assume that only one record is updated. Does anyone know a way to replicate MERGE behavior in older versions of SQL Server?

Answer 1

Alexey Kuznetsov’s blog contains a sentence using the OUTPUT clause of the UPDATE . To paraphrase the example from this blog post (unverified):

 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)