T-SQL - 将数据插入父表和子表 [英] T-SQL - Insert Data into Parent and Child Tables
问题描述
代码:
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
注意事项:
表变量被实时环境中的真实表替换.
The table variables are replaced by real tables in live environment.
EmployeeID 和 ParentEmployeeDepartmentID 列的值并不总是相互匹配.Live 环境在 udt (tEmployeeData) 中有更多记录，而不仅仅是 4
EmployeeID and ParentEmployeeDepartmentID columns' values don't always match each other. Live environment has more records in the udt (tEmployeeData) than just 4
目标:
udt (
tEmployeeData) 将被传入过程
程序应该首先将数据插入到@MainEmployee表中(并得到EmployeeIDs)
The procedure should first insert the data into the @MainEmployee table (and get the EmployeeIDs)
接下来，该过程应该将数据插入到 @ParentEmployeeDepartment 表中(并获取 ParentEmployeeDepartmentID) - 注意 EmployeeID 是来自之前的输出.
Next, the procedure should insert the data into the @ParentEmployeeDepartment table (and get the ParentEmployeeDepartmentID) - note EmployeeID is coming from the previous output.
然后，该过程应根据DepartmentType 拆分子级数据(A" = 插入@ChildEmployeeDepartmentTypeA 和B" = 插入@ChildEmployeeDepartmentTypeB).
Then, the procedure should split the child level data based on the DepartmentType ("A" = insert into @ChildEmployeeDepartmentTypeA and "B" = insert into @ChildEmployeeDepartmentTypeB).
ParentEmployeeDepartmentID>
程序应该运行得很快(需要避免逐行操作)
The procedure should should run fast (need to avoid row by row operation)
输出:
@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，但我不确定如何插入右子记录到右表中，并正确映射到父表.任何帮助将不胜感激.
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 我已在评论中链接到):
Here is my solution (based on the same answer I've linked to in the comments):
首先，您必须向 UDT 添加另一列，以保存员工的临时 ID:
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
使用新的 employeeId 列填充它:
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
插入部分在这里
然后，您使用表变量将员工表中插入的值映射到发送给过程的数据中的临时员工 ID:
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
)
现在，诀窍是使用 MERGE 填充员工表 语句而不是 INSERT...SELECT 因为您必须在输出子句中使用来自插入数据和源数据的值:
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
从那时起就很简单了，您需要加入发送到@EmployeeidMap 的数据以获取实际的employeeId:
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
现在您可以使用 @ParentEmployeeDepartment 中的数据将 ParentEmployeeDepartmentID 中的实际值映射到您发送的数据:
Now you can use the data in @ParentEmployeeDepartment to map the actual values in ParentEmployeeDepartmentID to the data you sent:
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
结果:
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
我相信从这一点上你可以很容易地自己找出最后 2 个插入.
I'm sure that from this point you can easily figure out the last 2 inserts yourself.
这篇关于T-SQL - 将数据插入父表和子表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
