How can I efficiently insert data into parent and child tables in a complex data structure using a stored procedure, ensuring proper data mapping and optimal performance?

A stored procedure for efficiently inserting parent-child table data into complex data structures

Question:

In a complex database structure, how to use stored procedures to efficiently insert data into parent-child tables while ensuring correct data mapping and optimizing performance?

Solution:

To efficiently insert data into parent-child tables, follow these steps:

1. Create a temporary ID column: Add a EmployeeId column in the user-defined table (UDT) to save the temporary ID of each employee.

2. Populate UDT with temporary ID: Insert data into UDT, including temporary ID.

3. Create EmployeeID mapping table: Declare a table variable (@EmployeeidMap) to map the value inserted from the employee table to the temporary employee ID in the UDT.

4. Use the MERGE statement to insert into the employee table: Use the MERGE statement to insert data into the @MainEmployee table, and output the source data employee ID and inserted employee ID to the mapping table.

5. Insert into ParentEmployeeDepartment table: Join the UDT with the mapping table, get the actual employee ID, and insert the data into the @ParentEmployeeDepartment table.

6. maps to ParentEmployeeDepartmentID: Using the data in @ParentEmployeeDepartment, map the actual value in ParentEmployeeDepartmentID to the data sent by the stored procedure.

7. Insert into subtable: Use Parent and EmployeeID from the DepartmentType table to determine into which subtable you want to insert data into.

Code:

<code class="language-sql">-- 创建EmployeeID映射表
DECLARE @EmployeeidMap TABLE (temp_id int, id int);

-- 使用MERGE语句插入员工表
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;

-- 插入ParentEmployeeDepartment表
INSERT INTO @ParentEmployeeDepartment (EmployeeID, DepartmentType)
SELECT Id, DepartmentType
FROM @tEmployeeData
INNER JOIN @EmployeeidMap ON EmployeeID = temp_id;

-- 映射到ParentEmployeeDepartmentID
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;</code>

Output:

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

Note:

This solution uses mapping tables to efficiently match different data between tables. It avoids row-by-row operations and optimizes performance, making it suitable for large data sets.

The above is the detailed content of How can I efficiently insert data into parent and child tables in a complex data structure using a stored procedure, ensuring proper data mapping and optimal performance?. For more information, please follow other related articles on the PHP Chinese website!