SQL Server 2008 MERGE 语法中的 USING 是什么?

2021-12-27 00:00:00 merge upsert sql-server sql-server-2008-r2

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

那里的每个答案都会立即跳转到他们能想到的最复杂的情况；用多余的混淆来掩盖语法.

Marc 给出了答案:

MERGEmember_topic AS 目标使用someOtherTable AS 源在target.mt_member = source.mt_memberAND source.mt_member = 0AND source.mt_topic = 110匹配时更新集 mt_notes = '测试'当不匹配时插入 (mt_member, mt_topic, mt_notes) VALUES (0, 110, 'test');

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

<块引用>

我没有 someOtherTable

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

<块引用>

我试过了，SQL Server确实抱怨

无效的对象名称someOtherTable".

这让我很难理解 USING foo 中的 USING 是什么 for ，如果它不重要(除非实际上很重要).>

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

<小时>

奖励问题

什么是使用 MERGE 的 UPSERT 语法:

IF (rowExists)更新用户 SET Firstname='Ian', LastName='Boyd' WHERE Username='iboyd'别的插入用户(UserGUID、用户名、名字、姓氏、AuthenticationMethod)值('{77410DC5-7A3E-4F1A-82C6-8EFB3068DE66}'、'iboyd'、'Ian'、'Boyd'、'Windows')

成为(我试过的确切代码):

开始交易合并用户使用富在用户.用户名 = foo.用户名匹配时更新 SET 名字 = foo.FirstName, Lastname = foo.LastName当不匹配时插入(用户GUID、用户名、名字、姓氏、身份验证方法)值('{77410DC5-7A3E-4F1A-82C6-8EFB3068DE66}'、'iboyd'、'Ian'、'Boyd'、'Windows');-- MERGE 语句必须以分号 (;) 结束.回滚Msg 208, Level 16, State 1, Line 3无效的对象名称foo".

<块引用>

使用包含列的 Users 表:

UserGUID 唯一标识符用户名 varchar(50)名字 varchar(50)姓氏 varchar(50)AuthenticationMethod varchar(50)

<小时>

更新:

使用

table_source 在哪里:

table_or_view_name [ [ AS ] table_alias ] [ ][ WITH ( table_hint [ [ , ]...n ] ) ]|rowset_function [ [ AS ] table_alias ][(bulk_column_alias [,...n])]|user_defined_function [ [ AS ] table_alias ]|OPENXML <openxml_clause>|派生表 [AS] 表别名 [(列别名 [,...n])]|<joined_table>|<pivoted_table>|<unpivoted_table>

joined_table 在哪里:

<块引用>

未定义

pivoted_table 在哪里:

<块引用>

未定义

unpivoted_table 在哪里:

<块引用>

未定义

解决方案

合并有一个表源和一个目标表.这引入了源表(不需要是实际的物理表，只是一个结果集).

语法已在您的问题中指明.从另一个表或视图合并使用

MERGE用户USING SomeOtherTableName AS foo/*别名是可选的*/在/* ... */

或者您可以使用例如

MERGE用户使用 master..spt_valuesUNPIVOT (X FOR Y IN ([high],[low])) AS foo在用户.用户名 = foo.Y匹配时更新 SET 名字 = foo.Y当不匹配时插入(用户GUID、用户名、名字、姓氏、身份验证方法)值 (foo.Y, foo.Y, foo.Y, foo.Y, foo.Y);

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

合并用户使用(值('{77410DC5-7A3E-4F1A-82C6-8EFB3068DE66}'，'iboyd','伊恩','博伊德','Windows')) AS foo(UserGUID, 用户名, FirstName, LastName, AuthenticationMethod)ON Users.UserName = foo.UserName匹配时更新 SET 名字 = foo.FirstName,姓氏 = foo.LastName当不匹配时插入(用户GUID，用户名，名，姓，身份验证方法)值(用户GUID，用户名，名，姓，身份验证方法);

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.

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

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

i try it, and SQL Server does complain

Invalid object name 'someOtherTable'.

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

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

Bonus Question

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

With a Users table that contains the columns:

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

Update:

USING <table_source>

Where table_source is:

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:

undefined

Where pivoted_table is:

undefined

Where unpivoted_table is:

undefined

解决方案

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.

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

相关文章