Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What is USING in SQL Server 2008 MERGE syntax?

Tags:

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

like image 566
Ian Boyd Avatar asked Jun 26 '12 21:06

Ian Boyd


People also ask

What is the syntax for MERGE in SQL?

Syntax: MERGE INTO target_table_name or target_table_query USING source_table_name or source_table_query ON (list_of_conditions) WHEN MATCHED THEN UPDATE target_table_name SET target_table_name.

What is the use of MERGE statement in SQL?

The MERGE statement in SQL is a very popular clause that can handle inserts, updates, and deletes all in a single transaction without having to write separate logic for each of these. You can specify conditions on which you expect the MERGE statement to insert, update, or delete, etc.

What is the use of MERGE command?

Use the MERGE statement to select rows from one or more sources for update or insertion into a table or view. You can specify conditions to determine whether to update or insert into the target table or view. This statement is a convenient way to combine multiple operations.

What is MERGE in SQL Server with example?

Runs insert, update, or delete operations on a target table from the results of a join with a source table. For example, synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table. MERGE is currently in preview for Azure Synapse Analytics.


2 Answers

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); 
like image 55
Martin Smith Avatar answered Nov 16 '22 07:11

Martin Smith


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);
like image 43
Marcel N. Avatar answered Nov 16 '22 06:11

Marcel N.