在SQL Server 2008 MERGE语法中使用什么？

Jacob asked the perfect question: give me the MERGE syntax.

雅各布问了一个完美的问题：给我MERGE语法。

Every answer out there immediately jumps to the most complicated case they can think of; obscuring the syntax with extraneous confusion.

那里的每一个答案都会立即跳到他们能想到的最复杂的案例中;用无关的混淆来掩盖语法。

Marc gave an answer:

马克给出了答案：

MERGE 
   member_topic AS target
USING 
   someOtherTable AS source
ON 
   target.mt_member = source.mt_member 
   AND source.mt_member = 0 
   AND source.mt_topic = 110
WHEN MATCHED THEN 
   UPDATE SET mt_notes = 'test'
WHEN NOT MATCHED THEN 
   INSERT (mt_member, mt_topic, mt_notes) VALUES (0, 110, 'test')
; 

Looking at this answer, i am as confused as Jacob was:

看着这个答案，我和雅各布一样困惑：

I don't have a someOtherTable

我没有someOtherTable

Marc suggested that someOtherTable is a dummy placeholder value - it doesn't matter that you don't have that table.

Marc建议someOtherTable是一个虚拟的占位符值 - 你没有那个表并不重要。

i try it, and SQL Server does complain

我尝试了，SQL Server确实抱怨

Invalid object name 'someOtherTable'.

无效的对象名称'someOtherTable'。

That leaves me struggling to understand what the USING in USING foo is for if it's not important (except actually important).

这让我很难理解使用foo的用法是什么，如果它不重要（除了实际重要）。

What is USING using when it's using foo when i use SQL Server 2008 MERGE syntax?

当我使用SQL Server 2008 MERGE语法时使用foo时使用什么？

---

Bonus Question

What is the UPSERT syntax using MERGE:

什么是使用MERGE的UPSERT语法：

IF (rowExists)
   UPDATE Users SET Firstname='Ian', LastName='Boyd' WHERE Username='iboyd'
ELSE
   INSERT INTO Users (UserGUID, Username, FirstName, LastName, AuthenticationMethod)
   VALUES ('{77410DC5-7A3E-4F1A-82C6-8EFB3068DE66}', 'iboyd', 'Ian', 'Boyd', 'Windows')

becomes (exact code i tried):

成为（我试过的确切代码）：

begin transaction

    MERGE 
       Users
    USING 
       foo
    ON  
       Users.UserName = foo.UserName
    WHEN MATCHED THEN
        UPDATE SET Firstname = foo.FirstName, Lastname = foo.LastName
    WHEN NOT MATCHED THEN
        INSERT (UserGUID, Username, FirstName, LastName, AuthenticationMethod)
        VALUES ('{77410DC5-7A3E-4F1A-82C6-8EFB3068DE66}', 'iboyd', 'Ian', 'Boyd', 'Windows')
    ; --A MERGE statement must be terminated by a semi-colon (;).

rollback

Msg 208, Level 16, State 1, Line 3
Invalid object name 'foo'.

?

？

With a Users table that contains the columns:

使用包含列的Users表：

UserGUID uniqueidentifier
Username varchar(50)
FirstName varchar(50)
LastName varchar(50)
AuthenticationMethod varchar(50)

---

Update:

更新：

USING <table_source> 

Where table_source is:

table_source的位置是：

table_or_view_name [ [ AS ] table_alias ] [ <tablesample_clause> ] 
    [ WITH ( table_hint [ [ , ]...n ] ) ] 
| rowset_function [ [ AS ] table_alias ] 
    [ ( bulk_column_alias [ ,...n ] ) ] 
| user_defined_function [ [ AS ] table_alias ]
| OPENXML <openxml_clause> 
| derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ] 
| <joined_table> 
| <pivoted_table> 
| <unpivoted_table> 

Where joined_table is:

其中joined_table是：

undefined

未定义

Where pivoted_table is:

其中pivoted_table是：

undefined

未定义

Where unpivoted_table is:

其中unpivoted_table是：

undefined

未定义

3 个解决方案

#1

A merge has a table source and a target table. This introduces the source table (which need not be an actual physical table, just a result set).

合并具有表源和目标表。这引入了源表（不一定是实际的物理表，只是结果集）。

The grammar is indicated in your question. To merge from another table or view use

语法在你的问题中表明。要从另一个表或视图合并使用

MERGE 
   Users
USING SomeOtherTableName AS foo /*Alias is optional*/
ON /* ... */

Or you can use <unpivoted_table> for example

或者您可以使用 作为示例

MERGE 
   Users
USING master..spt_values
UNPIVOT (X FOR Y IN ([high],[low])) AS foo 
ON  
   Users.Username = foo.Y 
WHEN MATCHED THEN
    UPDATE SET FirstName = foo.Y
WHEN NOT MATCHED THEN
    INSERT (UserGUID, Username, FirstName, LastName, AuthenticationMethod)
    VALUES (foo.Y, foo.Y, foo.Y, foo.Y, foo.Y);

For your bonus question you can use the VALUES clause here as part of the derived_table option.

对于您的奖金问题，您可以在此处使用VALUES子句作为derived_table选项的一部分。

MERGE Users
USING (VALUES ('{77410DC5-7A3E-4F1A-82C6-8EFB3068DE66}',
      'iboyd',
      'Ian',
      'Boyd',
      'Windows')) AS foo(UserGUID, Username, FirstName, LastName, AuthenticationMethod)
ON Users.UserName = foo.UserName
WHEN MATCHED THEN
  UPDATE SET Firstname = foo.FirstName,
             Lastname = foo.LastName
WHEN NOT MATCHED THEN
  INSERT (UserGUID,
          Username,
          FirstName,
          LastName,
          AuthenticationMethod)
  VALUES (UserGUID,
          Username,
          FirstName,
          LastName,
          AuthenticationMethod); 

#2

Source table can be anything, such as:

源表可以是任何内容，例如：

MERGE 
   member_topic AS target
USING 
   (SELECT @Variable1, @Variable2, @Variable3) AS source(Col1, Col2, Col3)
ON 
   target.mt_member = source.Col1 
   AND source.Col1 = 0 
   AND source.Col2 = 110
WHEN MATCHED THEN 
   UPDATE SET mt_notes = 'test'
WHEN NOT MATCHED THEN 
   INSERT (mt_member, mt_topic, mt_notes) VALUES (0, 110, 'test');

Obviously, in the nested source select you can do many more things. Select from a view, a function, a table variable, a CTE even.

显然，在嵌套源代码中你可以做更多的事情。从视图，函数，表变量，CTE中选择。

As for the bonus question, you answered your own question.

至于奖金问题，你回答了自己的问题。

Sometimes,for very large tables, I also use the ROWLOCK hint on the target table, to at least try not to lock the entire table in case of updates:

有时，对于非常大的表，我还在目标表上使用ROWLOCK提示，以便在更新时至少尝试不锁定整个表：

MERGE 
   member_topic WITH (ROWLOCK) AS target

Related to the bonus question not working, here's a working sample. I renamed some of the objects, of course.

与奖金问题无关，这是一个工作样本。当然，我重命名了一些对象。

DECLARE @Variable1 AS INT;
SET @Variable1 = 1234;

MERGE dbo.Table1 WITH(ROWLOCK) target
USING(SELECT @Variable1) source(Key)
ON target.[Key] = source.[Key]
WHEN MATCHED THEN
    UPDATE SET
    Col1 = @SomeVar1,
    Col2 = @SomeVar2
WHEN NOT MATCHED THEN
INSERT 
        ([Key]
        ,[Col1]
        ,[Col2])
    VALUES
        (@Variable1
        ,@SomeVar1
        ,@SomeVar2);

#3

Following on from Martin Smith's answer, you can upsert several explicit value rows at once simply by repeating the brackets, separating with a comma, eg,

根据Martin Smith的回答，您可以通过重复括号，用逗号分隔，例如，

MERGE Users WITH (HOLDLOCK)
USING (VALUES ('{77410DC5-7A3E-4F1A-82C6-8EFB3068DE66}',
      'iboyd',
      'Ian',
      'Boyd',
      'Windows'),
      ('{00000DC5-7A3E-4F1A-82C6-8EF452D2DE66}',
      'jsmith',
      'John',
      'Smith',
      'ActiveDirectory')) AS foo(UserGUID, Username, FirstName, LastName, AuthenticationMethod)
ON Users.UserName = foo.UserName
WHEN MATCHED THEN
  UPDATE SET Firstname = foo.FirstName,
             Lastname = foo.LastName
WHEN NOT MATCHED THEN
  INSERT (UserGUID,
          Username,
          FirstName,
          LastName,
          AuthenticationMethod)
  VALUES (UserGUID,
          Username,
          FirstName,
          LastName,
          AuthenticationMethod); 

Tested this on SQL Server 2012. (Would have added this as a comment but too many characters.)

在SQL Server 2012上测试过。（会将此添加为注释但字符太多。）

I added a HOLDLOCK having seen this, because if you're using MERGE for UPSERT surely the point is locking, the syntax is certainly no clearer. See also Marcel's comment on ROWLOCK for large tables.

我添加了一个看过这个的HOLDLOCK，因为如果你肯定使用MERGE for UPSERT锁定，那么语法肯定不会更清楚。另请参阅Marcel对大表的ROWLOCK的评论。

There was another post I found clearer than average, too.

还有另一篇文章我发现比平均更清晰。