SQL Server uses the merge statement to query problems when the source table dataset is empty _mssql

Give an example to specify:
There are two table SourceTable for the landing table, targettable for authorization table, if the access user in the landing table, Update authorization table authorization level, otherwise authorized 0 for visitors. The SQL statement is as follows:

--sourcetable for landing table, targettable for authorization table
-If the access user in the landing table, Update authorization table authorization level, otherwise authorize 0 to represent the visitor.
CREATE table SourceTable (UserName nvarchar (a), Pwd int,[level] int)
CREATE table targettable (UserName nvarchar (), [level] int)
Go
--Here are the new assignment features for SQL Server2008, and more features see

INSERT into sourcetable values (' User1 ', ' $ ', 1), (' User2 ', ' 222 ', 2), (' User3 ', ' 333 ', 3)
INSERT into targettable values (' User1 ', 1), (' User2 ', 2)

In general, we will write the following statement:

Copy Code code as follows:

DECLARE @UserName varchar = ' user2 '--Indicates the user name of a login
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]--Updating authorization level
When isn't matched by Target then
Insert (Username,[level]) VALUES (@UserName, 0)--Indicates that visitors
Output $action;

The execution result is update, which means that the update operation is performed, and that is exactly what we want.
But if we assign the @username to ' User6 ', we want to be able to insert a record in targettable, but the actual execution result is empty and shows nothing. The reason is the using (select Username,[level] from SourceTable where Username= @UserName) as SR (Username,[level]) on TG. USERNAME=SR. Username This statement, the SR result set is empty, so the merge statement is not executed backwards, and it is not known if this is a SQL Server bug.

The following SQL resolves the above problem:

Copy Code code as follows:

DECLARE @UserName varchar = ' user7 '--Indicates the user name of a login
Merge into targettable as TG
using (select @UserName)
As SR (UserName) on TG. USERNAME=SR. UserName
When matched then
Update Set [level]= (select 1 [level] from SourceTable where Username= @UserName)
--Update Authorization level
When isn't matched by Target then
Insert (Username,[level]) VALUES (@UserName, 0)--Indicates that visitors
Output $action;