title: How to use MERGE's 'When not matched' to insert a new record in same table?
body: <p>I am trying to do an upsert into a single table using SQL Server's Merge statement.</p>
  
  <pre><code>declare @Mergeoutput table (action varchar(50))
  
  declare variables ...
  
  MERGE Usertable AS target
  using (Select .... from Usertable where filter conditions using variables)
      as Source (column names..)
  on source.... = target.... (multiple statements)
  
  WHEN MATCHED THEN
    UPDATE SET....
  
  WHEN NOT MATCHED by target THEN
    INSERT (...)
       VALUES (...)
  output $action into @Mergeoutput;
  select * from @Mergeoutput
  </code></pre>
  
  <p>The update works if there's a match but the insert doesn't happen when there's no match. @Mergeout is empty. My update and insert statements should take action on one row. How does Merge work when the using statement is an empty set and I want to insert a new row?</p>
  
  <p><strong>Update:</strong><br>
  This is the SQL to run everything. I expected @Mergeoutput to return 'Insert' and the Person table to have a new row.</p>
  
  <pre><code>Create table Person
  (
    name varchar(20)
  )
  
  declare @Mergeoutput table (action varchar(50))
  
  declare @newName varchar(20)
  
  select @newName  = 'John'
  
  MERGE Person AS target
  using (Select name from Person where name= 'John')
      as Source (name)
  on source.name = target.name
  
  WHEN MATCHED THEN
    UPDATE SET name = 'John2'
  
  WHEN NOT MATCHED THEN
    INSERT (name)
       VALUES ('John')
  output $action into @Mergeoutput;
  select * from @Mergeoutput
  
  select * from person
  </code></pre>