如何在SQL中高效地向父子表插入資料？

高效插入SQL父子表資料

本文介紹一種高效率插入SQL父子表資料的方法，步驟如下：

1. 為UDT新增EmployeeId欄位

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. 填入EmployeeId欄位

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. 使用MERGE語句填入Employee表

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. 填入ParentEmployeeDepartment表

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

5. 填入ChildEmployeeDepartmentTypeA和ChildEmployeeDepartmentTypeB表

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. 輸出結果

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

以上是如何在SQL中高效地向父子表插入資料？的詳細內容。更多資訊請關注PHP中文網其他相關文章！