代码:
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
注意:
在实际环境中,表变量会被真实的表所替代。
EmployeeID
和ParentEmployeeDepartmentID
列的值并不总是相互匹配的。在 udt (tEmployeeData
) 中有比 4 条记录更多的记录。
目标:
将 udt (
tEmployeeData
) 传递到过程中。该过程应首先将数据插入到
@MainEmployee
表中(并获取EmployeeID
),然后,该过程应将数据插入到
@ParentEmployeeDepartment
表中(并获取ParentEmployeeDepartmentID
) - 注意,EmployeeID
来自前一个输出。接下来,该过程应根据
DepartmentType
(“A”= 插入到 @ChildEmployeeDepartmentTypeA,“B”= 插入到 @ChildEmployeeDepartmentTypeB)拆分子级数据。在插入数据到
@ChildEmployeeDepartmentTypeA
或@ChildEmployeeDepartmentTypeB
时应使用来自@ParentEmployeeDepartment
的ParentEmployeeDepartmentID
。该过程应运行快速(需要避免逐行操作)。
输出:
@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
我知道在插入之后可以使用OUTPUT
子句来获取EmployeeID
和ParentEmployeeDepartmentID
,但我不确定如何将正确的子记录插入到正确的表中,并与父表进行正确的映射。希望能得到帮助。