Bagaimana untuk Memasukkan Data dengan Cekap ke dalam Jadual Ibu Bapa dan Anak dalam SQL?

Sisipkan data jadual induk-anak SQL dengan cekap

Artikel ini memperkenalkan kaedah untuk memasukkan data ke dalam jadual induk-anak SQL dengan cekap adalah seperti berikut:

1. Tambahkan lajur EmployeeId ke 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. Isi lajur EmployeeId

<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 Gunakan pernyataan MERGE untuk mengisi jadual Pekerja

<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. Isi jadual Jabatan Ibu BapaPekerja

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

5 Isi jadual ChildEmployeeDepartmentTypeA dan ChildEmployeeDepartmentTypeB

<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. Hasil keluaran

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

Atas ialah kandungan terperinci Bagaimana untuk Memasukkan Data dengan Cekap ke dalam Jadual Ibu Bapa dan Anak dalam SQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!