如果存在则更新，否则在 SQL Server 2008 中插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21208719/
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
UPDATE if exists else INSERT in SQL Server 2008
提问by Maven
I want to know how can I use UPSERTor in other words UPDATE if records exists Else enter new recordoperation in SQL Server using one statement?
我想知道如何使用一个语句在 SQL Server 中使用UPSERT或换句话说UPDATE if records exists Else enter new record操作？
This example shows the ways of achieving this in Oracle HereBut it uses Dualtable for it which doesn't exists in SQL Server.
这个例子展示了在 Oracle Here中实现这一点的方法，
但它使用Dual了在SQL Server.
So, Any SQL Server Alternatives (No Stored procedures)please ?
那么，请提供任何 SQL Server 替代方案（无存储过程）？
回答by Aaron Bertrand
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/
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.sqlperformance.com/2012/08/t-sql-queries/error-handling
- 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
Not sure what you think you gain by having a single statement; I don't think you gain anything. MERGEis a single statement but it still has to really perform multiple operations anyway - even though it makes you think it doesn't.
不确定你认为通过一个单一的陈述你会得到什么；我不认为你有什么收获。MERGE是一个单一的语句，但无论如何它仍然必须真正执行多个操作 - 即使它让你认为它没有。
