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?