title: T-SQL - Insert Data into Parent and Child Tables
body: <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>