T-SQL - Insert Data into Parent and Child Tables

Question

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

Notes:

• The table variables are replaced by real tables in live environment.

• EmployeeID and ParentEmployeeDepartmentID columns' values don't always match each other. Live environment has more records in the udt (tEmployeeData) than just 4

Goal:

• The udt (tEmployeeData) will be passed into the procedure

• The procedure should first insert the data into the @MainEmployee table (and get the EmployeeIDs)

• Next, the procedure should insert the data into the @ParentEmployeeDepartment table (and get the ParentEmployeeDepartmentID) - note EmployeeID is coming from the previous output.

• Then, the procedure should split the child level data based on the DepartmentType ("A" = insert into @ChildEmployeeDepartmentTypeA and "B" = insert into @ChildEmployeeDepartmentTypeB).

• ParentEmployeeDepartmentID from @ParentEmployeeDepartment should be used when inserting data into either @ChildEmployeeDepartmentTypeA or @ChildEmployeeDepartmentTypeB

• The procedure should should run fast (need to avoid row by row operation)

Output:

@MainEmployee:

EmployeeID  FirstName   LastName
---------------------------------
1           Tom_FN      Tom_LN
2           Mike_FN     Mike_LN
3           Joe_FN      Joe_LN
4           Dave_FN     Dave_LN

@ParentEmployeeDepartment:

EmployeeID  ParentEmployeeDepartmentID  DepartmentType
-------------------------------------------------------
1           1                           A
2           2                           B
3           3                           A
4           4                           B

@ChildEmployeeDepartmentTypeA:

ParentEmployeeDepartmentID  DepartmentBuilding  DepartmentEmployeeLevel DepartmentTypeAMetadata
---------------------------------------------------------------------------------------------------------
1                           101                 IV                      Tech/IT
3                           101                 IV                      Tech/IT

@ChildEmployeeDepartmentTypeB:

ParentEmployeeDepartmentID  DepartmentBuilding  DepartmentEmployeeLevel DepartmentTypeAMetadata
----------------------------------------------------------------------------------------------------------
2                           OpenH               XII                     Med
4                           OpenC               XII                     Lab

I know I can use the OUTPUT clause after the insert and get EmployeeID and ParentEmployeeDepartmentID, 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.

Answer 1

Here is my solution (based on the same answer I've linked to in the comments):

First, you must add another column to your UDT, to hold a temporary ID for the employee:

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

Populating it with that new employeeId column:

DECLARE @tEmployeeData 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

Insert part goes here

Then, you use a table variable to map the inserted value from the employee table to the temp employee id in the data you sent to the procedure:

DECLARE @EmployeeidMap TABLE
(
    temp_id int,
    id int
)

Now, the trick is to populate the employee table with the MERGE statement instead of an INSERT...SELECT because you have to use values from both inserted and source data in the output clause:

MERGE INTO @MainEmployee USING @tEmployeeData AS sourceData ON 1 = 0 -- Always not matched
WHEN NOT MATCHED THEN
INSERT (FirstName, LastName)
VALUES (sourceData.FirstName, sourceData.LastName)
OUTPUT sourceData.EmployeeId, inserted.EmployeeID 
INTO @EmployeeidMap (temp_id, id); -- populate the map table

From that point on it's simple, you need to join the data you sent to the @EmployeeidMap to get the actual employeeId:

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

Now you can use the data in @ParentEmployeeDepartment to map the actual values in ParentEmployeeDepartmentID to the data you sent:

Testing the inserts so far

SELECT FirstName,
     LastName,
     SentData.DepartmentType As [Dept. Type],
     DepartmentBuilding As Building,
     DepartmentEmployeeLevel As [Emp. Level],
     DepartmentTypeAMetadata As [A Meta],
     DepartmentTypeBMetadata As [B Meta],
     SentData.EmployeeId As TempId, EmpMap.id As [Emp. Id], DeptMap.ParentEmployeeDepartmentID As [Dept. Id]
FROM @tEmployeeData SentData
INNER JOIN @EmployeeidMap EmpMap ON SentData.EmployeeId = temp_id 
INNER JOIN @ParentEmployeeDepartment DeptMap ON EmpMap.id = DeptMap.EmployeeID

results:

FirstName   LastName    Dept. Type  Building    Emp. Level  A Meta      B Meta  TempId      Emp. Id     Dept. Id
---------   --------    ----------  --------    ----------  ------      ------  ------      ----------- -----------
Dave_FN     Dave_LN     B           OpenC       XII         NULL        Lab     8           1           1
Joe_FN      Joe_LN      A           101         IV          Tech/IT     NULL    7           2           2
Mike_FN     Mike_LN     B           OpenH       XII         NULL        Med     6           3           3
Tom_FN      Tom_LN      A           101         IV          Tech/IT     NULL    5           4           4

I'm sure that from this point you can easily figure out the last 2 inserts yourself.