Wie fügt man Daten effizient in übergeordnete und untergeordnete Tabellen in SQL ein?

Effizientes Einfügen von SQL-Eltern-Kind-Tabellendaten

In diesem Artikel wird eine Methode zum effizienten Einfügen von Daten in SQL-Eltern-Kind-Tabellen vorgestellt. Die Schritte sind wie folgt:

1. Spalte „EmployeeId“ zum UDT hinzufügen

<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. Füllen Sie die Spalte „EmployeeId“ aus

<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. Verwenden Sie die MERGE-Anweisung, um die Mitarbeitertabelle zu füllen

<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. Füllen Sie die ParentEmployeDepartment-Tabelle aus

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

5. Füllen Sie die Tabellen ChildEmployeeDepartmentTypeA und ChildEmployeDepartmentTypeB

<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. Ergebnisse ausgeben

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

Das obige ist der detaillierte Inhalt vonWie fügt man Daten effizient in übergeordnete und untergeordnete Tabellen in SQL ein?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!