TSQL Merge on clause and when clause understanding

The ON clause of the Merge specifies that the match condition,when clause specifies the filter condition, which is well understood if the source table and Targe table match, and if not, you must understand the mismatched condition in depth, otherwise it is prone to error.

1. Create Sample Data

Create TableDbo.dt_source (IDint, Codeint)GoCreate TableDbo.dt_target (IDint, Codeint)GoInsert  intoDbo.dt_source (Id,code)Values(1,1),(2,1),(3,2),(4,2),(5,0)GOInsert  intodbo.dt_target (Id,code)Values(1,1),(6,4)GO

2, the SourceTable is filtered using the additional filter criteria (S.CODE>0) in the merge's on clause. The intention was to synchronize the data code>0 in SourceTable to targettable as a data source, but in the ON clause of the merge, S.code>0 was just a match condition;

The not matched clause refers to the t.id=s.id and s.id>0 that are not satisfied, and the logical result is: T.id<>s.id or s.id<=0, which dbo.dt_source in id<=0 satisfies not Matched semantics, so it is inserted into the dbo.dt_target.

; Merge Dbo.dt_target astusing Dbo.dt_source ass onT.id=S.id andS.code>0 whenmatched Then UpdateSetT.code=S.code when  notmatched Then Insert(Id,code)Values(S.id,s.code);

View Targettable,code=0 data is inserted into the Targetable table with the following data:

When synchronizing data with the merge clause, the SourceTable filter is placed in the When clause, and if the filter condition is not added to the When clause, the entire sourcetable is synchronized.

; Merge Dbo.dt_target astusing Dbo.dt_source ass onT.id=S.id
 whenmatched and s.code>0    Then UpdateSetT.code=S.code when  notMatched andS.code>0 Then Insert(Id,code)Values(S.id,s.code);

TSQL Merge on clause and when clause understanding