How to Efficiently Insert Data into Parent and Child Tables with Correct Mapping?

Insert data into the parent-child table

The goal of this example is to insert data from an incoming UDT (@tEmployeeData) into multiple tables, ensuring correct mapping between parent and child records.

Solving mapping issues

To establish the necessary mapping, we add a temporary employeeId column to the UDT and fill it with continuous values. Additionally, we create a table variable (@EmployeeidMap) to map the temporary employee ID in the UDT with the actual employee ID generated in the target table.

Populate the main table

We utilize MERGE to populate the @MainEmployee table with values ​​from the inserted data and source data. This allows us to capture the generated EmployeeID value and populate the mapping table at the same time.

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

Map and insert into parent-child tables

We then join @tEmployeeData with @EmployeeidMap to get the actual employee ID, which we use to populate the @ParentEmployeeDepartment table. Similarly, we connect @ParentEmployeeDepartment with @tEmployeeData to map parent ID to child data and insert the data into @ChildEmployeeDepartmentTypeA and @ChildEmployeeDepartmentTypeB.

Test insert

To verify that the mapping was successful, we execute a query that joins the UDT data with the mapping and target tables.

SELECT FirstName,
     LastName,
     SentData.DepartmentType As [部门类型],
     DepartmentBuilding As 建筑物,
     DepartmentEmployeeLevel As [员工级别],
     DepartmentTypeAMetadata As [A 元数据],
     DepartmentTypeBMetadata As [B 元数据],
     SentData.EmployeeId As 临时ID, EmpMap.id As [员工ID], DeptMap.ParentEmployeeDepartmentID As [部门ID]
FROM @tEmployeeData SentData
INNER JOIN @EmployeeidMap EmpMap ON SentData.EmployeeId = temp_id 
INNER JOIN @ParentEmployeeDepartment DeptMap ON EmpMap.id = DeptMap.EmployeeID

This approach ensures that child data is correctly mapped to its respective parent record, while optimizing performance by avoiding unnecessary row-by-row operations.

The above is the detailed content of How to Efficiently Insert Data into Parent and Child Tables with Correct Mapping?. For more information, please follow other related articles on the PHP Chinese website!