SQL Server 2005，批量更新或插入 [英] SQL Server 2005, bulk UPDATE or INSERT

问题描述

我正在寻找一种在 SQL Server 2005 中对重复键更新操作执行插入的解决方案.此操作可能会插入或更新大量条目.SQL Server 2008 有一个简洁的 MERGE 操作，可以完美地完成它，问题是我们坚持使用 SQL Server 2005.
我已经研究了标准解决方案，但它们都不好，因为他们假设只有一个条目被更新/插入.
有人知道在旧版本的 SQL Server 中复制 MERGE 行为的方法吗?

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?

推荐答案

Alex Kuznetsov 的博客 包含使用 UPDATE 语句的 OUTPUT 子句的建议.转述该博客条目中的示例(未经测试):

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)

这篇关于SQL Server 2005，批量更新或插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！