SQL Server cannot query the source table when the data set of the source table is empty using the Merge statement.

The following is an example:
Two tables, SourceTable and TargetTable, are logon tables. If the user accesses the login table, update the authorization level of the authorization table. Otherwise, authorization 0 indicates the visitor. The SQL statement is as follows:

-- SourceTable: logon table, TargetTable: Authorization Table
-- If the visitor updates the authorization level of the authorization table in the login table, otherwise, authorization 0 indicates the visitor.
Create table SourceTable (UserName nvarchar (10), Pwd int, [Level] int)
Create table TargetTable (UserName nvarchar (10), [Level] int)
Go
-- The assignment function added in SQL Server2008 is as follows. For more new functions, see

Insert into SourceTable values ('user1', '000000', 1), ('user2', '000000', 2), ('user3', '000000', 3)
Insert into TargetTable values ('user1', 1), ('user2', 2)

In general, we will write the following statement:

Copy codeThe Code is as follows:
Declare @ UserName varchar (10) = 'user2' -- indicates the user name for 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] -- update authorization Level
When not matched by target then
Insert (UserName, [Level]) values (@ UserName, 0) -- represents a visitor
Output $ action;


If the execution result is UPDATE, the UPDATE operation is executed, which is exactly what we want.
However, if we assign @ UserName to 'user6', we hope to insert a record in TargetTable, but the actual execution result is blank, and nothing is executed. The reason is that using (select UserName, [Level] from SourceTable where UserName = @ UserName) as sr (UserName, [Level]) on tg. userName = sr. in the UserName statement, the sr result set is empty, so the merge statement will not be executed backward. I wonder if this is a SQL Server bug.

The following SQL statement can solve the above problem:

Copy codeThe Code is as follows:
Declare @ UserName varchar (10) = 'user7' -- indicates the user name for 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 top 1 [Level] from SourceTable where UserName = @ UserName)
-- Update the authorization level
When not matched by target then
Insert (UserName, [Level]) values (@ UserName, 0) -- represents a visitor
Output $ action;