T-SQL 是否可以通过单个快速操作进行更新/插入 [英] T-SQL Is it possible to do an Update / Insert with a single fast operation

问题描述

假设我有一个表格，我想插入一行.新行的键可能已经与表中现有行的键匹配，在这种情况下，我想更新现有行.或者，它可能不存在于表中，在这种情况下应该插入新行.

Let's say I have a table and I want to insert a row. The new row's key may already match an existing row's key in the table, in which case I want to update the existing row. Or, it may not exist in the table, in which case the new row should be inserted.

执行此类操作的最有效方法是什么?我想先做一个 SELECT(也许用 EXISTS)来查看是否存在特定的键，然后是 UPDATE(如果存在)和INSERT 如果没有.您可能还需要为这种语句组合保留一个 UPDLOCK 和一个 HOLDLOCK 以避免竞争条件.这似乎过于复杂和低效.

What is the most efficient way to perform such an operation? I was thinking of first doing a SELECT (perhaps with EXISTS) to see if a particular key is present, followed by an UPDATE if present and an INSERT if not. You would probably need to keep an UPDLOCK and a HOLDLOCK for this combination of statements in order to avoid race conditions, as well. This seems overly complicated and inefficient.

我想知道在 SQL Server 2008R2 中是否有更有效的方法来做到这一点.

I was wondering if there was a more efficient way to do this in SQL Server 2008R2.

推荐答案

SQL Server 2008 和更新版本有一个 MERGE 语句，正好可以做到这一点.

SQL Server 2008 and newer have a MERGE statement which does exactly that.

有关详细信息，请参阅 有关 MERGE 的 MSDN 联机丛书文档.

See the MSDN Books Online docs on MERGE for details.

基本上，您需要四件事:

Basically, you need four things:

• 源(表或视图或内联 SELECT 语句)
• 一个目标
• 连接两者的JOIN条件
• 针对 MATCH(源和目标中都存在行)、NOT MATCHED(目标中尚不存在行)等情况的语句

• a source (table or view or inline SELECT statement)
• a target
• a JOIN condition that links the two
• statements for cases when there's a MATCH (rows exists in both source and target), NOT MATCHED (when row doesn't exist in the target yet) and so forth

所以你基本上定义如下:

So you basically define something like:

MERGE (targettable) AS t
USING (sourcetable) AS s
ON (JOIN condition between s and t)
WHEN MATCHED THEN
   UPDATE SET t.Col1 = s.Col1, t.Col2 = s.Col2 (etc.)
WHEN NOT MATCHED THEN
   INSERT(Col1, Col2, ..., ColN) VALUES(s.Col1, s.Col2, ......, s.ColN)

这是作为一个语句完成的，并由 SQL Server 高度优化.

This is done as one statement and highly optimized by SQL Server.

这篇关于T-SQL 是否可以通过单个快速操作进行更新/插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！