T-SQL - Insert Data into Parent and Child Tables
<p>Code:</p>

<pre><code>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)
)
GO

CREATE PROC dbo.EmployeeImport 
    (@tEmployeeData tEmployeeData READONLY)
AS
BEGIN
     DECLARE @MainEmployee TABLE 
                           (EmployeeID INT IDENTITY(1,1),
                            FirstName NVARCHAR(50),
                            LastName NVARCHAR(50))

    DECLARE @ParentEmployeeDepartment TABLE 
                                      (EmployeeID INT,
                                       ParentEmployeeDepartmentID INT IDENTITY(1,1),
                                       DepartmentType NVARCHAR(10))

    DECLARE @ChildEmployeeDepartmentTypeA TABLE 
                  (ParentEmployeeDepartmentID INT,
                   DepartmentBuilding NVARCHAR(50),
                   DepartmentEmployeeLevel NVARCHAR(10),
                   DepartmentTypeAMetadata NVARCHAR(100))

    DECLARE @ChildEmployeeDepartmentTypeB TABLE 
                  (ParentEmployeeDepartmentID INT,
                   DepartmentBuilding NVARCHAR(50),
                   DepartmentEmployeeLevel NVARCHAR(10),
                   DepartmentTypeBMetadata NVARCHAR(100))

    -- INSERT CODE GOES HERE
    SELECT * FROM @MainEmployee
    SELECT * FROM @ParentEmployeeDepartment
    SELECT * FROM @ChildEmployeeDepartmentTypeA
    SELECT * FROM @ChildEmployeeDepartmentTypeB
END
GO

DECLARE @tEmployeeData tEmployeeData

INSERT INTO @tEmployeeData (FirstName, LastName, DepartmentType,
                            DepartmentBuilding, DepartmentEmployeeLevel,
                            DepartmentTypeAMetadata, DepartmentTypeBMetadata)
    SELECT  
        N'Tom_FN', N'Tom_LN', N'A',
        N'101', N'IV', N'Tech/IT', NULL
    UNION
    SELECT  
        N'Mike_FN', N'Mike_LN', N'B',
        N'OpenH', N'XII', NULL, N'Med' 
    UNION
    SELECT  
        N'Joe_FN', N'Joe_LN', N'A',
        N'101', N'IV', N'Tech/IT', NULL
    UNION
    SELECT  
        N'Dave_FN', N'Dave_LN', N'B',
        N'OpenC', N'XII', NULL, N'Lab' 

    EXEC EmployeeImport @tEmployeeData
GO

DROP PROC dbo.EmployeeImport 
DROP TYPE dbo.tEmployeeData
</code></pre>

<p>Notes:</p>

<ul>
<li><p>The table variables are replaced by real tables in live environment.</p></li>
<li><p><code>EmployeeID</code> and <code>ParentEmployeeDepartmentID</code> columns' values don't always match each other. Live environment has more records in the udt (<code>tEmployeeData</code>) than just 4</p></li>
</ul>

<p>Goal:</p>

<ul>
<li><p>The udt (<code>tEmployeeData</code>) will be passed into the procedure </p></li>
<li><p>The procedure should first insert the data into the <code>@MainEmployee</code> table (and get the <code>EmployeeID</code>s) </p></li>
<li><p>Next, the procedure should insert the data into the <code>@ParentEmployeeDepartment</code> table (and get the <code>ParentEmployeeDepartmentID</code>) - note <code>EmployeeID</code> is coming from the previous output. </p></li>
<li><p>Then, the procedure should split the child level data based on the <code>DepartmentType</code> ("A" = insert into @ChildEmployeeDepartmentTypeA and "B" = insert into @ChildEmployeeDepartmentTypeB).    </p></li>
<li><p><code>ParentEmployeeDepartmentID</code> from <code>@ParentEmployeeDepartment</code> should be used when inserting data into either <code>@ChildEmployeeDepartmentTypeA</code> or <code>@ChildEmployeeDepartmentTypeB</code></p></li>
<li><p>The procedure should should run fast (need to avoid row by row operation)</p></li>
</ul>

<p>Output:</p>

<p><strong>@MainEmployee</strong>:</p>

<pre><code>EmployeeID  FirstName   LastName
---------------------------------
1           Tom_FN      Tom_LN
2           Mike_FN     Mike_LN
3           Joe_FN      Joe_LN
4           Dave_FN     Dave_LN
</code></pre>

<p><strong>@ParentEmployeeDepartment</strong>:</p>

<pre><code>EmployeeID  ParentEmployeeDepartmentID  DepartmentType
-------------------------------------------------------
1           1                           A
2           2                           B
3           3                           A
4           4                           B
</code></pre>

<p><strong>@ChildEmployeeDepartmentTypeA</strong>:</p>

<pre><code>ParentEmployeeDepartmentID  DepartmentBuilding  DepartmentEmployeeLevel DepartmentTypeAMetadata
---------------------------------------------------------------------------------------------------------
1                           101                 IV                      Tech/IT
3                           101                 IV                      Tech/IT
</code></pre>

<p><strong>@ChildEmployeeDepartmentTypeB</strong>:</p>

<pre><code>ParentEmployeeDepartmentID  DepartmentBuilding  DepartmentEmployeeLevel DepartmentTypeAMetadata
----------------------------------------------------------------------------------------------------------
2                           OpenH               XII                     Med
4                           OpenC               XII                     Lab
</code></pre>

<p>I know I can use the <code>OUTPUT</code> clause after the insert and get <code>EmployeeID</code> and <code>ParentEmployeeDepartmentID</code>, but I'm not sure how to insert the right child records into right tables with right mapping to the parent table. Any help would be appreciated.</p>