SQL Server 2008 MERGE语法中的用法是什么? [英] What is USING in SQL Server 2008 MERGE syntax?

问题描述

Jacob提出了一个完美的问题: 给我MERGE语法 .

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

那里的每个答案都会立即跳到他们能想到的最复杂的情​​况；不必要地混淆了语法.

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

马克给答案:

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:

我没有someOtherTable

I don't have a someOtherTable

Marc建议someOtherTable是一个虚拟占位符值-没那个表没关系.

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

我尝试了，SQL Server 做了抱怨

无效的对象名称"someOtherTable".

Invalid object name 'someOtherTable'.

这让我很难理解USING foo中的USING对于 的意义(如果不重要的话).

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

当我使用SQL Server 2008 MERGE语法时，USING在使用 foo 时使用的是什么?

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

使用MERGE的UPSERT语法是什么?

What is the UPSERT syntax using MERGE:

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'.

?

具有包含列的Users表:

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

---

更新:

USING <table_source> 

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> 

joined_table所在的位置:

未定义

pivoted_table所在的位置:

未定义

unpivoted_table所在的位置:

未定义

推荐答案

合并具有表源和目标表.这将介绍源表(不必是实际的物理表，而只是结果集).

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 /* ... */

或者您可以使用 <unpivoted_table> 例如

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);

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

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

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); 

这篇关于SQL Server 2008 MERGE语法中的用法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！