sql server - How to use MERGE's 'When not matched' to insert a new record in same table? -

i trying upsert single table using sql server's merge statement.

declare @mergeoutput table (action varchar(50))  declare variables ...  merge usertable target using (select .... usertable filter conditions using variables)     source (column names..) on source.... = target.... (multiple statements)  when matched   update set....  when not matched target   insert (...)      values (...) output $action @mergeoutput; select * @mergeoutput 

the update works if there's match insert doesn't happen when there's no match. @mergeout empty. update , insert statements should take action on 1 row. how merge work when using statement empty set , want insert new row?

update:
sql run everything. expected @mergeoutput return 'insert' , person table have new row.

create table person (   name varchar(20) )  declare @mergeoutput table (action varchar(50))  declare @newname varchar(20)  select @newname  = 'john'  merge person target using (select name person name= 'john')     source (name) on source.name = target.name  when matched   update set name = 'john2'  when not matched   insert (name)      values ('john') output $action @mergeoutput; select * @mergeoutput  select * person 

usually, merge statement this:

merge @person target using (     select name      @person      <.......> ) source on source.name = target.name  when matched update set name = source.name  when not matched insert (name) values (source.name) ; 

note, take values source , insert them target.

when not matched [ target ]

specifies row inserted target_table every row returned <table_source> on <merge_search_condition> not match row in target_table, satisfy additional search condition, if present. values insert specified <merge_not_matched> clause.

so, if source empty, nothing inserted, regardless of on criteria.

it not clear overall logic trying achieve, seems may need have logic in source query.