How to use merge statement in SQL Server

This article is about how to use the merge sentence in SQL Server. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article.

Let me give an example to illustrate: there are two tables: SourceTable is the login table, and TargetTable is the authorization table. If the visiting user updates the authorization level of the authorization form in the login form, otherwise the authorization 0 represents the visitor. The sql statement is as follows:-- SourceTable is the login table, TargetTable is the authorization table-if the visiting user is in the login table, update the authorization level of the authorization table, otherwise authorization 0 means tourists. Create table SourceTable (UserName nvarchar (10), Pwd int, [Level] int) create table TargetTable (UserName nvarchar (10), [Level] int) go-- here are the new assignment functions of SQL Server2008. For more new features, please see insert into SourceTable values ('user1','111',1), (' user2','222',2), ('user3','333',3) insert into TargetTable values (' user1',1), ('user2',2) General situation. We will write the following statement: the copy code is as follows: declare @ UserName varchar (10) = 'user2'-- indicates a login user name merge into TargetTable as tg using (select UserName, [Level] from SourceTable where UserName=@UserName) as sr (UserName, [Level]) on tg.UserName=sr.UserName when matched then update set [Level] = sr.[ level]-- updates the authorization level when not matched by target then insert (UserName, [Level]) values (@ UserName,0)-- indicates the visitor output $action

The result of execution is UPDATE, which means that the update operation has been performed, which is exactly what we want. But if we assign @ UserName to 'user6', wants to insert a record in the TargetTable, but the actual execution result is empty, indicating that nothing has been executed. The reason is that in the statement using (select UserName, [Level] from SourceTable where UserName=@UserName) as sr (UserName, [Level]) on tg.UserName=sr.UserName, the sr result set is empty, so the merge statement is not executed backward. I don't know if this is the bug of SQL Server. The following SQL can solve the above problem: the copy code is as follows: declare @ UserName varchar (10) = 'user7'-- indicates a login user name merge into TargetTable as tg using (select @ UserName) as sr (UserName) on tg.UserName=sr.UserName when matched then update set [Level] = (select top 1 [Level] from SourceTable where UserName=@UserName)-- updates the authorization level when not matched by target then insert (UserName, [Level]) values (@ UserName,0)-- indicates the visitor output $action

The above is how to use the merge statement in SQL Server. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.