How to Efficiently Insert Data into Parent and Child Tables in SQL?

Efficiently insert SQL parent-child table data

This article introduces a method to efficiently insert data into SQL parent-child tables. The steps are as follows:

1. Add EmployeeId column to UDT

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

2. Populate the EmployeeId column

DECLARE @tEmployeeData dbo.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;

3. Use the MERGE statement to populate the Employee table

DECLARE @EmployeeidMap TABLE (temp_id INT, id INT);
MERGE INTO @MainEmployee AS target
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);

4. Populate the ParentEmployeeDepartment table

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

5. Populate the ChildEmployeeDepartmentTypeA and ChildEmployeeDepartmentTypeB tables

INSERT INTO @ChildEmployeeDepartmentTypeA (ParentEmployeeDepartmentID, DepartmentBuilding, DepartmentEmployeeLevel, DepartmentTypeAMetadata)
SELECT ParentEmployeeDepartmentID, DepartmentBuilding, DepartmentEmployeeLevel, DepartmentTypeAMetadata
FROM @tEmployeeData
INNER JOIN @EmployeeidMap ON EmployeeID = temp_id
WHERE DepartmentType = 'A';

INSERT INTO @ChildEmployeeDepartmentTypeB (ParentEmployeeDepartmentID, DepartmentBuilding, DepartmentEmployeeLevel, DepartmentTypeBMetadata)
SELECT ParentEmployeeDepartmentID, DepartmentBuilding, DepartmentEmployeeLevel, DepartmentTypeBMetadata
FROM @tEmployeeData
INNER JOIN @EmployeeidMap ON EmployeeID = temp_id
WHERE DepartmentType = 'B';

6. Output results

SELECT * FROM @MainEmployee;
SELECT * FROM @ParentEmployeeDepartment;
SELECT * FROM @ChildEmployeeDepartmentTypeA;
SELECT * FROM @ChildEmployeeDepartmentTypeB;

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