这是我的解决方案(基于相同的 answer 我已在评论中链接到):

Here is my solution (based on the same answer I've linked to in the comments):

首先，您必须向 UDT 添加另一列，以保存员工的临时 ID:

First, you must add another column to your UDT, to hold a temporary ID for the employee:

CREATE TYPE dbo.tEmployeeData AS TABLE 
(
     FirstName NVARCHAR(50),
     LastName NVARCHAR(50),
     DepartmentType NVARCHAR(10),
     DepartmentBuilding NVARCHAR(50),
     DepartmentEmployeeLevel NVARCHAR(10),
     DepartmentTypeAMetadata NVARCHAR(100),
     DepartmentTypeBMetadata NVARCHAR(100),
     EmployeeId int
)
GO

使用新的 employeeId 列填充它:

Populating it with that new employeeId column:

DECLARE @tEmployeeData tEmployeeData

INSERT INTO @tEmployeeData (FirstName, LastName, DepartmentType,
                            DepartmentBuilding, DepartmentEmployeeLevel,
                            DepartmentTypeAMetadata, DepartmentTypeBMetadata, EmployeeId)
SELECT  
    N'Tom_FN', N'Tom_LN', N'A',
    N'101', N'IV', N'Tech/IT', NULL, 5
UNION
SELECT  
    N'Mike_FN', N'Mike_LN', N'B',
    N'OpenH', N'XII', NULL, N'Med', 6
UNION
SELECT  
    N'Joe_FN', N'Joe_LN', N'A',
    N'101', N'IV', N'Tech/IT', NULL, 7
UNION
SELECT  
    N'Dave_FN', N'Dave_LN', N'B',
    N'OpenC', N'XII', NULL, N'Lab', 8

插入部分在这里

然后，您使用表变量将员工表中插入的值映射到发送给过程的数据中的临时员工 ID:

Insert part goes here

Then, you use a table variable to map the inserted value from the employee table to the temp employee id in the data you sent to the procedure:

DECLARE @EmployeeidMap TABLE
(
    temp_id int,
    id int
)

现在，诀窍是使用 MERGE 填充员工表 语句而不是 INSERT...SELECT 因为您必须在输出子句中使用来自插入数据和源数据的值:

Now, the trick is to populate the employee table with the MERGE statement instead of an INSERT...SELECT because you have to use values from both inserted and source data in the output clause:

MERGE INTO @MainEmployee USING @tEmployeeData AS sourceData ON 1 = 0 -- Always not matched
WHEN NOT MATCHED THEN
INSERT (FirstName, LastName)
VALUES (sourceData.FirstName, sourceData.LastName)
OUTPUT sourceData.EmployeeId, inserted.EmployeeID 
INTO @EmployeeidMap (temp_id, id); -- populate the map table

从那时起就很简单了，您需要加入发送到@EmployeeidMap 的数据以获取实际的employeeId:

From that point on it's simple, you need to join the data you sent to the @EmployeeidMap to get the actual employeeId:

INSERT INTO @ParentEmployeeDepartment (EmployeeID, DepartmentType)
SELECT Id, DepartmentType
FROM @tEmployeeData 
INNER JOIN @EmployeeidMap ON EmployeeID = temp_id

现在您可以使用 @ParentEmployeeDepartment 中的数据将 ParentEmployeeDepartmentID 中的实际值映射到您发送的数据:

Now you can use the data in @ParentEmployeeDepartment to map the actual values in ParentEmployeeDepartmentID to the data you sent:

SELECT FirstName,
     LastName,
     SentData.DepartmentType As [Dept. Type],
     DepartmentBuilding As Building,
     DepartmentEmployeeLevel As [Emp. Level],
     DepartmentTypeAMetadata As [A Meta],
     DepartmentTypeBMetadata As [B Meta],
     SentData.EmployeeId As TempId, EmpMap.id As [Emp. Id], DeptMap.ParentEmployeeDepartmentID As [Dept. Id]
FROM @tEmployeeData SentData
INNER JOIN @EmployeeidMap EmpMap ON SentData.EmployeeId = temp_id 
INNER JOIN @ParentEmployeeDepartment DeptMap ON EmpMap.id = DeptMap.EmployeeID

结果:

FirstName   LastName    Dept. Type  Building    Emp. Level  A Meta      B Meta  TempId      Emp. Id     Dept. Id
---------   --------    ----------  --------    ----------  ------      ------  ------      ----------- -----------
Dave_FN     Dave_LN     B           OpenC       XII         NULL        Lab     8           1           1
Joe_FN      Joe_LN      A           101         IV          Tech/IT     NULL    7           2           2
Mike_FN     Mike_LN     B           OpenH       XII         NULL        Med     6           3           3
Tom_FN      Tom_LN      A           101         IV          Tech/IT     NULL    5           4           4

我相信从这一点上你可以很容易地自己找出最后 2 个插入.

I'm sure that from this point you can easily figure out the last 2 inserts yourself.