What is USING in SQL Server 2008 MERGE syntax?

Question

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

Answer 1

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,

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

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.

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

Answer 2

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

Answer 3

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.

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:

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