我编写了一个存储过程，如果存在记录则会进行更新，否则它会进行插入。 它看起来像这样：
1
2
3
| UPDATE myTable SET Col1=@col1, Col2=@col2 WHERE ID=@ID
IF @@rowcount = 0
INSERT INTO myTable (Col1, Col2) VALUES (@col1, @col2) |
我以这种方式编写它的逻辑是更新将使用where子句执行隐式选择，如果返回0，则插入将发生。
以这种方式执行此操作的替代方法是执行select，然后根据返回的行数执行更新或插入。 我认为这是低效的，因为如果要进行更新，将导致2次选择(第一次显式选择调用，第二次隐式更新位置)。 如果proc要进行插入，那么效率没有区别。
我的逻辑是否在这里？这是如何将插入和更新组合到存储过程中的？
你的假设是正确的，这是做到这一点的最佳方式，它被称为upsert / merge。
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.
编辑：
请查看此答案和链接的博客文章，了解此模式的问题以及如何使其安全工作。
请阅读我博客上的帖子，了解您可以使用的安全模式。有很多考虑因素，这个问题的公认答案远非安全。
要快速回答，请尝试以下模式。它将在SQL 2000及更高版本上正常工作。 SQL 2005为您提供了错误处理，从而打开了其他选项，SQL 2008为您提供了MERGE命令。
1
2
3
4
5
6
7
8
9
10
| 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 |
如果要与SQL Server 2000/2005一起使用，则需要在事务中包含原始代码，以确保数据在并发方案中保持一致。
1
2
3
4
5
| 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 |
这将产生额外的性能成本，但将确保数据完整性。
如上所述，添加MERGE应该在可用的地方使用。
您不仅需要在事务中运行它，还需要高隔离级别。事实上，默认隔离级别是Read Commited，此代码需要Serializable。
1
2
3
4
5
6
7
8
| 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 |
也许添加@@错误检查和回滚可能是个好主意。
顺便说一句，MERGE是SQL Server 2008中的新功能之一。
如果您没有在SQL 2008中进行合并，则必须将其更改为：
if @@ rowcount = 0和@@ error = 0
否则，如果更新由于某种原因失败，那么它将尝试之后插入，因为失败语句上的rowcount为0
UPSERT的忠实粉丝，真正减少了要管理的代码。这是我这样做的另一种方式：其中一个输入参数是ID，如果ID为NULL或0，你知道它是一个INSERT，否则它是一个更新。假设应用程序知道是否有ID，所以在所有情况下都不会工作，但如果你这样做会将执行减少一半。
修改后的Dima Malenko帖子：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
| 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 |
您可以捕获错误并将记录发送到失败的插入表。
我需要这样做，因为我们正在通过WSDL发送任何数据，如果可能的话，在内部修复它。
您的逻辑似乎很合理，但如果您传入了特定的主键，则可能需要考虑添加一些代码以防止插入。
否则，如果您在更新不影响任何记录时总是进行插入，那么当您在"UPSERT"运行之前删除记录时会发生什么？现在您尝试更新的记录不存在，因此它将创建一个记录。这可能不是你想要的行为。
