在不使用存储过程的情况下在SQL Server 2005上插入/更新

I'm trying to do the classic Insert/Update scenario where I need to update existing rows in a database or insert them if they are not there.

我正在尝试执行经典的插入/更新方案,我需要更新数据库中的现有行,或者如果它们不存在则插入它们。

I've found a previous question on the subject, but it deals with stored procedures, which I'm not using. I'd like to just use plain SQL SELECT, INSERT and UPDATE statements, unless there's something better available (the MERGE statement isn't available in SQL Server 2005).

我已经找到了关于这个主题的上一个问题,但它处理的是存储过程,我没有使用它。我想只使用简单的SQL SELECT,INSERT和UPDATE语句,除非有更好的可用(MERGE语句在SQL Server 2005中不可用)。

I guess my general idea is this:

我想我的一般想法是这样的:

If the row is found
  update
else
  insert

As for checking for a row's existence, how expensive is it to do a SELECT statement before calling an UPDATE or an INSERT? Or is it better to just try an UPDATE, check for the number of rows affected, and then do an INSERT if the rows affected is 0?

至于检查行的存在,在调用UPDATE或INSERT之前执行SELECT语句有多昂贵?或者只是尝试UPDATE更好,检查受影响的行数,然后在受影响的行为0时执行INSERT?

4 个解决方案

#1

The most efficient way is to do the UPDATE, then do an INSERT if @@rowcount is zero, as explained in this previous answer.

最有效的方法是进行UPDATE,然后在@@ rowcount为零时执行INSERT,如上一个答案中所述。

#2

(First of all - I would not try to avoid stored procedures, if there is no strong reason. The give a good benefit in most cases.)

(首先 - 如果没有充分的理由,我不会试图避免存储过程。在大多数情况下,这样做会带来很好的好处。)

You could do it this way:

你可以这样做:

create a (temporary) table

创建一个(临时)表

fill in your rows

填写你的行

run a INTERSECT that identifies the extisting rows

运行一个INTERSECT来识别排出的行

update your table with them

用它们更新你的表

run a EXCEPT that identifies the new rows

运行标识新行的EXCEPT

run an insert with these elements

运行带有这些元素的插入

drop/clear your (temporary) table

删除/清除您的(临时)表

This will probably run faster, if you are inserting/updating large amounts of rows.

如果要插入/更新大量行,这可能会运行得更快。

#3

Completely understand that your post was titled "SQL Server 2005" but just wanted to throw out something to look forward to if/when you upgrade to SQL 2008. Microsoft has added a new MERGE statement which will give you the ability to code one DML statement that does both the update and insert. It's pretty cool. I've yet to compare performance and I/Os but it's just great to have another tool in your toolbox.

完全理解您的帖子标题为“SQL Server 2005”,但只是想在升级到SQL 2008时抛出一些值得期待的东西.Microsoft已经添加了一个新的MERGE语句,它将使您能够编写一个DML语句同时进行更新和插入。它太酷了。我还没有比较性能和I / O,但是在工具箱中使用另一个工具真的很棒。

#4

-1

If you are always going to:
* Count the rows
* Insert / Update based on the result

如果您总是要:*计算行数*根据结果插入/更新

Why not instead:
* Delete row
* Insert row

为什么不改为:*删除行*插入行

Same result and neater.
As far as I know when you Update a row - SQLServer does a Delete / Insert anyway (where it exists)

相同的结果和整洁。据我所知,当您更新行时 - SQLServer无论如何都会执行删除/插入(存在的位置)

#1