What is USE in SQL Server 2008 MERGE Syntax?

Question

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

Each answer there immediately goes into the most difficult case that they may think of; hiding syntax with extraneous confusion.

Mark answered :

 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'm as confused as Jacob is:

I don't have someOtherTable

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

I am trying to do this and SQL Server is complaining

Invalid object name 'someOtherTable'.

This leaves me trying to figure out what USING in USING foo means if it doesn't matter (other than the really important ones).

What is USING used with foo when using the MERGE SQL Server 2008 syntax?

---

Bonus Question

What is UPSERT syntax with 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 (the 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 containing columns:

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

---

Update:

 USING <table_source> 

Where 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 :

undefined

Where pivoted_table :

undefined

Where unpivoted_table :

undefined

Answer 1

A merge has a table source and a target table. This introduces the source table (which does not have to be a real physical table, just a set of results).

Your question indicates grammar. 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 2

The source table can be any, for example:

 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 a nested source element, you can do much more. Choose from a view, function, table variable, even CTE.

Regarding the question about the bonus, you answered your question.

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

 MERGE member_topic WITH (ROWLOCK) AS target 

The question related to the bonus does not work, here is a working sample. Of course, I renamed some objects.

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

Answer 3

Following Martin Smith’s answer, you can immediately add multiple lines of values ​​at once, simply repeating the brackets, separating them with a comma, for example

 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 on SQL Server 2012. (I would add this as a comment, but too many characters.)

I added HOLDLOCK when I saw this , because if you use MERGE for UPSERT, of course, the point is blocked, the syntax is certainly not clearer. See also Marcel's comment on ROWLOCK for large tables.

There was another message I found more clear than average, too.