Outputting source column in MERGE OUTPUT clause

Question

I have a relatively simple question with an implied answer, but not an explicit one. Here's the background.

Here are the 3 schemas I'm working with:

--Source Data: 
ProjectID, ProjectName, CompanyName

--SQL Tables:
Project ( ProjectID [PK\Identity], ProjectName, CompanyID )
Company ( CompanyID [PK\Identity], CompanyName )

Project.CompanyID is a FK to Company.CompanyID. All pretty basic.

Now because I have to transform CompanyName in the source data into its normalised form, insertion is a multi-step process. The first step is to merge into Company:

MERGE INTO [Company] AS tgt
    USING (SELECT CompanyName FROM [Source Data]) as src (CompanyName)
    ON tgt.CompanyName = src.CompanyName
    WHEN NOT MATCHED THEN  
        INSERT (CompanyName)  
        VALUES (src.CompanyName)
    OUTPUT inserted.CompanyID
    ;

In a nutshell, if the CompanyName isn't in the table, insert it, and output the identity value. Sensible enough, but not actually useful because the result of the OUTPUT clause isn't ordered.

Here's my approach to that:

MERGE INTO [Company] AS tgt
    USING (SELECT ProjectID, CompanyName FROM [Source Data]) as src (ProjectID, CompanyName)
    ON tgt.CompanyName = src.CompanyName
    WHEN NOT MATCHED THEN  
        INSERT (CompanyName)  
        VALUES (src.CompanyName)
    OUTPUT inserted.CompanyID, src.ProjectID
    ;

By using the from_table_name syntactical element in the dml_select_list, I can (theoretically) establish a direct relationship between a ProjectID and CompanyID and use that in the second insert into the Project table.

My question is - can I actually trust that relationship?

The documentation is not well written (https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver16):

A column prefix that specifies a table included in the FROM clause of a DELETE, UPDATE, or MERGE statement that is used to specify the rows to update or delete.

I'm not using a FROM clause for one, and I'm inserting, not updating or deleting for two. It also doesn't actually describe the relationship between the columns. I can assume or infer that there is a direct correlation between inserted.CompanyID and src.ProjectID because without that, the functionality of using a from_table_name seems kind of useless.

So does anyone know the exact nature of the underlying join between inserted and from_table_name?

Answer 1

My question is - can I actually trust that relationship?

You can trust that the inserted.CompanyID and src.ProjectID values are associated with the same source and target row.

From the documentaton:

WHEN NOT MATCHED [ BY TARGET ] THEN <merge_not_matched>

Specifies that a row is inserted into target_table for every row returned by <table_source> ON <merge_search_condition> that doesn't match a row in target_table, but satisfies an additional search condition, if present. The values to insert are specified by the <merge_not_matched> clause. The MERGE statement can have only one WHEN NOT MATCHED [ BY TARGET ] clause.

In other words, for this MERGE clause, each source row either results in an INSERT action or it doesn't. This is the linkage I think you're after: An inserted target row is associated with exactly one source row.

Whether the source row matches the target is determined by the ON clause. When a row is inserted, any source row values referenced come from the single source row. Any identity value generated is associated with the inserted row. The one-to-one relationship between the two does the rest.

So does anyone know the exact nature of the underlying join between inserted and from_table_name?

There isn't a join as such. Both are ways of referencing values associated with each affected row. The inserted prefix specifies post-change values in the target table row. The from_table_name prefix qualifies non-target attributes in the FROM clause (or, in the case of a MERGE, the USING clause).

---

In general, MERGE prevents multiple source rows causing actions on the same target row. If the statement and schema don't guarantee this, additional operators are added to the execution plan to detect the condition and raise an error if it occurs at runtime.

The one exception is where multiple source rows cause a delete action on the same target row. In that case, only the first action is performed; the duplicates are discarded. Multiple updates or any combination of update and delete to the same target row are not permitted.

To complete the picture, multiple target rows can be affected by a single source row using some MERGE clauses. In that case, OUTPUT data can be constructed from the particular target row's attributes and the source row, if present. There is no source row for WHEN NOT MATCHED BY SOURCE, so source column references are not allowed in that case.

For more details, see Inserting, Updating, and Deleting Data by Using MERGE in the retired documentation.