在 SQL Server 上插入更新存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13540/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert Update stored proc on SQL Server
提问by Guy
I've written a stored proc that will do an update if a record exists, otherwise it will do an insert. It looks something like this:
我编写了一个存储过程，如果记录存在，它将进行更新，否则它将进行插入。它看起来像这样：
update myTable set Col1=@col1, Col2=@col2 where ID=@ID
if @@rowcount = 0
insert into myTable (Col1, Col2) values (@col1, @col2)
My logic behind writing it in this way is that the update will perform an implicit select using the where clause and if that returns 0 then the insert will take place.
我以这种方式编写它背后的逻辑是更新将使用 where 子句执行隐式选择，如果返回 0，则将进行插入。
The alternative to doing it this way would be to do a select and then based on the number of rows returned either do an update or insert. This I considered inefficient because if you are to do an update it will cause 2 selects (the first explicit select call and the second implicit in the where of the update). If the proc were to do an insert then there'd be no difference in efficiency.
以这种方式执行此操作的另一种方法是进行选择，然后根据返回的行数进行更新或插入。我认为这是低效的，因为如果您要进行更新，它将导致 2 个选择（第一个显式选择调用和第二个隐式在更新的位置）。如果 proc 进行插入，那么效率就没有区别。
Is my logic sound here? Is this how you would combine an insert and update into a stored proc?
我的逻辑在这里吗？这是将插入和更新组合到存储过程中的方式吗？
采纳答案by binOr
Your assumption is right, this is the optimal way to do it and it's called upsert/merge.
您的假设是正确的，这是执行此操作的最佳方法，它称为upsert/merge。
Importance of UPSERT - from sqlservercentral.com:
UPSERT 的重要性 - 来自 sqlservercentral.com：
For every update in the case mentioned above we are removing one additional read from the table if we use the UPSERT instead of EXISTS. Unfortunately for an Insert, both the UPSERT and IF EXISTS methods use the same number of reads on the table. Therefore the check for existence should only be done when there is a very valid reason to justify the additional I/O. The optimized way to do things is to make sure that you have little reads as possible on the DB.
The best strategy is to attempt the update. If no rows are affected by the update then insert. In most circumstances, the row will already exist and only one I/O will be required.
对于上述案例中的每个更新，如果我们使用 UPSERT 而不是 EXISTS，我们将从表中删除一个额外的读取。不幸的是，对于插入，UPSERT 和 IF EXISTS 方法在表上使用相同数量的读取。因此，只有在有非常有效的理由证明附加 I/O 的合理性时，才应检查是否存在。做事的优化方法是确保您对数据库的读取尽可能少。
最好的策略是尝试更新。如果没有行受更新影响，则插入。在大多数情况下，该行已经存在并且只需要一个 I/O。
Edit: Please check out this answerand the linked blog post to learn about the problems with this pattern and how to make it work safe.
编辑：请查看此答案和链接的博客文章，以了解此模式的问题以及如何使其安全工作。
回答by Sam Saffron
Please read the post on my blogfor a good, safe pattern you can use. There are a lot of considerations, and the accepted answer on this question is far from safe.
请阅读我博客上的帖子，了解您可以使用的良好、安全的模式。有很多考虑，关于这个问题的公认答案远非安全。
For a quick answer try the following pattern. It will work fine on SQL 2000 and above. SQL 2005 gives you error handling which opens up other options and SQL 2008 gives you a MERGE command.
要快速回答，请尝试以下模式。它将在 SQL 2000 及更高版本上正常工作。SQL 2005 为您提供了错误处理，它打开了其他选项，SQL 2008 为您提供了一个 MERGE 命令。
begin tran
update t with (serializable)
set hitCount = hitCount + 1
where pk = @id
if @@rowcount = 0
begin
insert t (pk, hitCount)
values (@id,1)
end
commit tran
回答by Dima Malenko
If to be used with SQL Server 2000/2005 the original code needs to be enclosed in transaction to make sure that data remain consistent in concurrent scenario.
如果要与 SQL Server 2000/2005 一起使用，原始代码需要包含在事务中，以确保数据在并发场景中保持一致。
BEGIN TRANSACTION Upsert
update myTable set Col1=@col1, Col2=@col2 where ID=@ID
if @@rowcount = 0
insert into myTable (Col1, Col2) values (@col1, @col2)
COMMIT TRANSACTION Upsert
This will incur additional performance cost, but will ensure data integrity.
这将产生额外的性能成本，但会确保数据完整性。
Add, as already suggested, MERGE should be used where available.
添加，正如已经建议的那样，应在可用的情况下使用 MERGE。
回答by Jon Galloway
MERGE is one of the new features in SQL Server 2008, by the way.
顺便说一下，MERGE 是 SQL Server 2008 中的新功能之一。
回答by Tomas Tintera
You not only need to run it in transaction, it also needs high isolation level. I fact default isolation level is Read Commited and this code need Serializable.
您不仅需要在事务中运行它，还需要高隔离级别。事实上，默认隔离级别是 Read Commited，并且此代码需要可序列化。
SET transaction isolation level SERIALIZABLE
BEGIN TRANSACTION Upsert
UPDATE myTable set Col1=@col1, Col2=@col2 where ID=@ID
if @@rowcount = 0
begin
INSERT into myTable (ID, Col1, Col2) values (@ID @col1, @col2)
end
COMMIT TRANSACTION Upsert
Maybe adding also the @@error check and rollback could be good idea.
也许添加@@error 检查和回滚可能是个好主意。
回答by Simon Munro
If you are not doing a merge in SQL 2008 you must change it to:
如果您不是在 SQL 2008 中进行合并，则必须将其更改为：
if @@rowcount = 0 and @@error=0
如果@@rowcount = 0 和@@error=0
otherwise if the update fails for some reason then it will try and to an insert afterwards because the rowcount on a failed statement is 0
否则，如果更新由于某种原因失败，那么它会在之后尝试插入，因为失败语句上的行数为 0
回答by Natron
Big fan of the UPSERT, really cuts down on the code to manage. Here is another way I do it: One of the input parameters is ID, if the ID is NULL or 0, you know it's an INSERT, otherwise it's an update. Assumes the application knows if there is an ID, so wont work in all situations, but will cut the executes in half if you do.
UPSERT 的忠实拥护者，确实减少了要管理的代码。这是我做的另一种方法：其中一个输入参数是 ID，如果 ID 为 NULL 或 0，则您知道它是 INSERT，否则它是更新。假设应用程序知道是否有一个 ID，所以不会在所有情况下都工作，但如果你这样做，就会将执行次数减半。
回答by Kevin Fairchild
Your logic seems sound, but you might want to consider adding some code to prevent the insert if you had passed in a specific primary key.
您的逻辑看起来很合理，但如果您传入了特定的主键，您可能需要考虑添加一些代码来防止插入。
Otherwise, if you're always doing an insert if the update didn't affect any records, what happens when someone deletes the record before you "UPSERT" runs? Now the record you were trying to update doesn't exist, so it'll create a record instead. That probably isn't the behavior you were looking for.
否则，如果您总是在更新不影响任何记录的情况下执行插入操作，那么当有人在您运行“UPSERT”之前删除记录时会发生什么？现在您尝试更新的记录不存在，因此它将创建一个记录。这可能不是您要寻找的行为。
回答by thughes78013
Modified Dima Malenko post:
修改后的 Dima Malenko 帖子：
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION UPSERT
UPDATE MYTABLE
SET COL1 = @col1,
COL2 = @col2
WHERE ID = @ID
IF @@rowcount = 0
BEGIN
INSERT INTO MYTABLE
(ID,
COL1,
COL2)
VALUES (@ID,
@col1,
@col2)
END
IF @@Error > 0
BEGIN
INSERT INTO MYERRORTABLE
(ID,
COL1,
COL2)
VALUES (@ID,
@col1,
@col2)
END
COMMIT TRANSACTION UPSERT
You can trap the error and send the record to a failed insert table.
I needed to do this because we are taking whatever data is send via WSDL and if possible fixing it internally.
您可以捕获错误并将记录发送到失败的插入表。
我需要这样做，因为我们正在获取通过 WSDL 发送的任何数据，并在可能的情况下进行内部修复。
