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?
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
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.
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.
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.
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.
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);
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);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With