Way to do MERGE with update source when target not match?

Updates are tracked by MERGE by providing:

  • the $Action value UPDATE;
  • the old values in the memory table deleted; and
  • the new values in the memory table inserted.

Therefore both in the case where a match is found, and in the case where there is no match by target, the values of the identity column is available in the inserted memory table.

You've done a lot with one night's experience with MERGE statements!

The DELETED column prefix of the OUTPUT clause should contain the values updated in the MERGE UPDATE SET statements, I think you're on the right track with Attempt 2.