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

<code class="language-sql">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</code>

2. Populate the EmployeeId column

<code class="language-sql">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;</code>

3. Use the MERGE statement to populate the Employee table

<code class="language-sql">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);</code>

4. Populate the ParentEmployeeDepartment table

<code class="language-sql">INSERT INTO @ParentEmployeeDepartment (EmployeeID, DepartmentType)
SELECT Id, DepartmentType
FROM @tEmployeeData
INNER JOIN @EmployeeidMap ON EmployeeID = temp_id;</code>

5. Populate the ChildEmployeeDepartmentTypeA and ChildEmployeeDepartmentTypeB tables

<code class="language-sql">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';</code>

6. Output results

<code class="language-sql">SELECT * FROM @MainEmployee;
SELECT * FROM @ParentEmployeeDepartment;
SELECT * FROM @ChildEmployeeDepartmentTypeA;
SELECT * FROM @ChildEmployeeDepartmentTypeB;</code>

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!