SQL中父表和子表的大容量插入操作

Question

对于我们当前的项目(. Net Core)，我们需要迁移旧数据，其中的数据是在一个excel表的纯格式。我们需要导入数据，然后将这些数据创建到数据库中。在此迁移中涉及多个表。假设表为:

table Parent(
Id UNIQUEIDENTIFIER PRIMARY KEY,
Name varchar(120),
value varchar(150)
)
table Child1(
Id UNIQUEIDENTIFIER PRIMARY KEY,
childvalue1 varchar(120),
childvalue2 varchar(150),
P_ID UNIQUEIDENTIFIER foreign_key # has an index
)
table Child2(
Id UNIQUEIDENTIFIER PRIMARY KEY,
child2value1 varchar(200),
P_ID UNIQUEIDENTIFIER foreign_key # has an index
)

关系为:父节点有一对多的Child1,Child2.

父表有对表a的引用。

excel表格中的数据格式为:Name - value - childvalue1 - childvalue2 - child2value1

这种格式有数千行。

我想做的是:

• 复制excel数据到表
• 创建要插入父表和子表的存储过程

我担心的是:

• 如何将父id映射到子id，同时逐行上传数据?
• 插入条目时也必须生成Id, Id类型为GUID

Answer 1

对于从Excel file到database的bulk insert，如果您想拥有一个高性能的应用程序，请遵循此方法。

1. 首先，创建一个temp table对应于你的Excel文件列和一个InsertionKey的名称的列。

2. 用相同的InsertionKey(生成一个新的GUID)从excel中插入所有输入数据，所有excel行将具有相同的键。使用实体框架BulkInsertAsync或SqlBulkCopy进行批量插入。你也可以检查我的GitHub仓库的基准测试BulkInsert在。net)

3. 插入数据后，将GUID作为参数发送给存储过程

现在，您可以通过使用键从临时表中获取所有插入的数据来轻松地处理它们。

:

你的excel文件列:

Item1   Red      25
Item2   Black    32
Item3   Orange   44

在大容量插入前生成一个新的Guid

var insertionKey = Guid.NewGuid();

现在批量插入数据库:

Item1   Red      25    insertionKey
Item2   Black    32    insertionKey
Item3   Orange   44    insertionKey

现在用Insertionkey 调用存储过程

YourStoredProcedure(Insertionkey)

存储过程内部:

select *  
into #temp 
from temptable 
where insertionKey = insertionKeyParameter

在这里你可以读取插入的行，然后按照你想要的方式处理它们。

Answer 2

这里有一个示例脚本，它给出了如何做到这一点的概念。它依赖于值的唯一性，也就是说，给定的名称/值只有一个含义

首先需要将excel数据加载到Stage表中。

然后可以使用T-SQL将这些值分配到三个表中。通过连接回值，您可以检索父id的

注意这里没有循环，它是一个基于集合的解。

DECLARE @Parent TABLE (
Id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT newsequentialid() NOT NULL,
Name varchar(120),
value varchar(150)
)
DECLARE @Child1 TABLE(
Id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT newsequentialid() NOT NULL,
childvalue1 varchar(120),
childvalue2 varchar(150),
P_ID UNIQUEIDENTIFIER
)
DECLARE @Child2 TABLE (
Id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT newsequentialid() NOT NULL,
child2value1 varchar(200),
P_ID UNIQUEIDENTIFIER
)

-- This is the stage table that excel data is loaded into
DECLARE @Stage  TABLE (
COL1 VARCHAR(100),
COL2 VARCHAR(100),
COL3 VARCHAR(100),
COL4 VARCHAR(100),
COL5 VARCHAR(100)
)
-- Load sample data
INSERT INTO @Stage(COL1,COL2,COL3,COL4,COL5)
VALUES 
('Name1','value1','childvalue1','childvalue2','child2value1'),
('Name1','value1','childvalue3','childvalue4','child4value1'),
('Name1','value1','childvalue3','childvalue4','child4value2'),
('Name1','value1','childvalue3','childvalue4','child4value3'),
('Name2','value2','childvalue8','childvalue5','child5value1'),
('Name2','value2','childvalue8','childvalue6','child6value1'),
('Name2','value2','childvalue8','childvalue6','child6value2')
-- Save top level, generating id's
INSERT INTO @Parent(Name,Value)
SELECT DISTINCT COL1,COL2 FROM @Stage
-- save children, looking up id's based on actual values
INSERT INTO @Child1(P_ID,childvalue1,childvalue2)
SELECT DISTINCT P.Id, S.COL3,S.COL4 
FROM @Stage S
INNER JOIN @Parent P
-- relies on these values being unique
ON S.COL1 = P.Name
AND S.COL2 = P.value
-- Check results    
SELECT * FROM @Parent
SELECT * FROM @Child1