如果存在则更新，否则在 SQL Server 2008 中插入 [英] UPDATE if exists else INSERT in SQL Server 2008

问题描述

我想知道如何使用 UPSERT 或者换句话说 UPDATE if records exists Else enter new record 使用一条语句在 SQL Server 中操作?

I want to know how can I use UPSERT or in other words UPDATE if records exists Else enter new record operation in SQL Server using one statement?

此示例显示了在 Oracle 此处中实现此目的的方法但是它使用了 Dual 表，它在 SQL Server 中不存在.

This example shows the ways of achieving this in Oracle Here But it uses Dual table for it which doesn't exists in SQL Server.

那么，任何 SQL Server 替代方案(无存储过程)好吗?

So, Any SQL Server Alternatives (No Stored procedures) please ?

推荐答案

很多人会建议你使用 MERGE，但我警告你不要这样做.默认情况下，它不会像多个语句一样保护您免受并发和竞争条件的影响，但它确实引入了其他危险:

Many people will suggest you use MERGE, but I caution you against it. By default, it doesn't protect you from concurrency and race conditions any more than multiple statements, but it does introduce other dangers:

http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/

即使有了这种更简单"的语法，我仍然更喜欢这种方法(为简洁起见省略了错误处理):

Even with this "simpler" syntax available, I still prefer this approach (error handling omitted for brevity):

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
UPDATE dbo.table SET ... WHERE PK = @PK;
IF @@ROWCOUNT = 0
BEGIN
  INSERT dbo.table(PK, ...) SELECT @PK, ...;
END
COMMIT TRANSACTION;

很多人会这样建议:​​

A lot of folks will suggest this way:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
IF EXISTS (SELECT 1 FROM dbo.table WHERE PK = @PK)
BEGIN
  UPDATE ...
END
ELSE
BEGIN
  INSERT ...
END
COMMIT TRANSACTION;

但是所有这些完成的是确保您可能需要读取表两次才能找到要更新的行.在第一个示例中，您只需要定位行一次.(在这两种情况下，如果没有从初始读取中找到行，就会发生插入.)

But all this accomplishes is ensuring you may need to read the table twice to locate the row(s) to be updated. In the first sample, you will only ever need to locate the row(s) once. (In both cases, if no rows are found from the initial read, an insert occurs.)

其他人会这样建议:​​

Others will suggest this way:

BEGIN TRY
  INSERT ...
END TRY
BEGIN CATCH
  IF ERROR_NUMBER() = 2627
    UPDATE ...
END CATCH

但是，如果除了让 SQL Server 捕获您本来可以阻止的异常更昂贵之外，没有其他原因，这是有问题的，除非在几乎每个插入都失败的罕见情况下.我在这里证明了:

However, this is problematic if for no other reason than letting SQL Server catch exceptions that you could have prevented in the first place is much more expensive, except in the rare scenario where almost every insert fails. I prove as much here:

• http://www.mssqltips.com/sqlservertip/2632/checking-for-potential-constraint-violations-before-entering-sql-server-try-and-catch-logic/
• http://www.sqlperformance.com/2012/08/t-sql-queries/错误处理

不确定你认为通过一个单一的陈述你会得到什么；我不认为你有什么收获.MERGE 是一个单一的语句，但无论如何它仍然必须真正执行多个操作 - 即使它让您认为它没有.

Not sure what you think you gain by having a single statement; I don't think you gain anything. MERGE is a single statement but it still has to really perform multiple operations anyway - even though it makes you think it doesn't.

这篇关于如果存在则更新，否则在 SQL Server 2008 中插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！