如果SQL Server 2008中存在else插入，则进行更新[duplicate]

This question already has an answer here:

这个问题已经有了答案:

Solutions for INSERT OR UPDATE on SQL Server 21 answers
SQL Server 21上的插入或更新解决方案

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?

我想知道如何使用UPSERT或者换句话说，如果记录存在，那么如何使用一条语句在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.

这个例子展示了在Oracle中实现这一点的方法，但是它使用了SQL Server中不存在的双表。

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

那么，有SQL Server替代方案(没有存储过程)吗?

1 个解决方案

#1

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:

许多人会建议您使用MERGE，但我警告您不要这么做。默认情况下，它并不比多个语句更能保护您不受并发和竞态条件的影响，但它确实引入了其他危险:

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

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:

然而，如果除了让SQL Server捕获您本来可以避免的异常之外，没有其他原因，这就会产生问题，除非是在几乎所有插入都失败的罕见场景中。我在这里证明了很多:

http://www.mssqltips.com/sqlservertip/2632/checking-for-potential-constraint-violations-before-entering-sql-server-try-and-catch-logic/
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/error-handling
http://www.sqlperformance.com/2012/08/t-sql-queries/error-handling

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.

不确定你认为你能从一个单一的陈述中得到什么;我认为你什么也没得到。MERGE是一个单独的语句，但无论如何它仍然必须执行多个操作——即使它让您认为它不是。

#1