8

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.

Improve this question
5
  • USE OUTPUT inserted.EmployedId, inserted.ParentEmployeeDepartmentType INTO TempTable (employeeid, ...) then do 2 insert statements for the child where department = A and then the next where department = b. Commented Jul 5, 2016 at 22:38
  • You might want to look at this answer. My solution was to use a different UDT for each table, but it is possible to convert it to use a single UDT if you must. Commented Jul 6, 2016 at 4:33
  • @Matt, how do I, for example, prevent child data mis-mapping of ParentEmployeeDepartmentID of 2 with 4? ZoharPeled, I'll look into that solution. The issue with this scenario is, I think, that we don't have an unique "id" field for Main/Parent tables. Commented Jul 6, 2016 at 5:23
  • Looking at it further yes you will need a temporary key. That can be done by dumping your UDT into a temp table and assign an identity or expanding your udt to hold an identity as Zohar suggests. Without a key you would have to rely on First & Last name combination being unique which in a small setting may be true but wouldn't hold up for long. While I am not always a fan of MERGE I do like Zohar's use of it to maintain the relationship between the keys. Commented Jul 6, 2016 at 16:44
  • @matt thanks, I feel the same way about merge. However, that's the only way I know of to get the output from both the source table and the inserted data. Commented Jul 7, 2016 at 3:10

1 Answer 1

Reset to default
13

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

this is brilliant!!! Thanks for the MERGE Output clause option tip (vs INSERT). Pretty cool, thank you!!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.