与下面提到的其他问题类似,我有两个具有以下结构的表:
create table parent (
recno int identity(1,1) primary key not null,
groupCode int,
parentdata varchar(80)
);
create table child (
parentrecno int not null,
childdata varchar(80)
)
我需要将几十万条记录快速插入到这些表中——这些表包含数百万条与此插入无关的其他记录,并且永远不会安静。由于父母/子女的性质,它(似乎)不是SqlBulkCopy
的好候选者。
在使用SqlCommand
和INSERT
的C#中,我每秒插入大约400-500条记录,这有点太慢了。伪码:
foreach(Record r in parentRecords)
{
Insert Fields from r into SqlCommand Parameters but not "recno"
Call ExecuteScalar to insert and fetch the inserted identity value (recno)
foreach(ChildRecord cr in parentRecords.Children)
{
Insert Fields from cr into SqlCommand Parameters
Insert the identity value (recno) from above into Parameters
(as parentrecno)
Call ExecuteNonQuery to insert the record
}
}
在阅读了其他帖子后,我想到了一个问题。附加到父记录的groupCode
对于我插入的那组父记录是唯一的。它能起作用吗:
- 使用
SqlBulkCopy
大容量插入父记录,让插入像往常一样自动生成recno
标识字段 仅对插入的记录执行
SELECT
:select recno from parent where groupCode = @thisgroup order by recno;
使用检索到的值填充内存中子记录的
parentrecno
字段- 使用
SqlBulkCopy
大容量插入子记录
这将依赖于父记录以与原始DataTable中相同的顺序进入SQL表(以及以相同顺序分配的标识值)这是我可以信赖的东西吗
相关问题:
如何更新数据集父级&具有自动生成的标识密钥的子表?
标识列上具有父子关系的SqlBulkCopy和DataTables
创建两个与目标表结构相同但不在recno列上使用标识的暂存表。
create table parentTmp (
recno int,
groupCode int,
parentdata varchar(80)
);
create table childTmp (
parentrecno int not null,
childdata varchar(80)
)
将数据大容量加载到暂存表,保持recno/pparentrecno值不变。
然后,您可以使用合并和输出来从暂存表中移动数据。
-- Table variable to hold mapping between
-- SourceRecno and TargetRecno
declare @recno table(SourceRecno int, TargetRecno int);
-- Merge data from parentTmp to parent
-- Output old and new recno to @recno
merge parent T
using parentTmp S
on 0=1
when not matched then
insert (groupCode, parentdata)
values (S.groupCode, S.parentData)
output S.recno, inserted.recno into @recno;
-- Copy data from childTmp to child
-- Use @recno to get the new recno
insert into child(parentrecno, childdata)
select R.TargetRecno, C.childdata
from childTmp as C
inner join @recno as R
on C.parentrecno = R.SourceRecno;
这只会在SQLServer2008中起作用(我想以后也会这样)。
这不是一个绝对的大容量插入,而是将所有子数据与父数据同时插入,只需往返数据库一次。
insert into parent(groupcode, parentdata) values(1, 'parent data');
insert into child(parentrecno, childdata) select parentrecno, childdata from (
select SCOPE_IDENTITY() as parentrecno, 'child data 1' as childdata
union
select SCOPE_IDENTITY() as parentrecno, 'child data 2' as childdata
union
select SCOPE_IDENTITY() as parentrecno, 'child data 3' as childdata
) childrendata;
您可以在C#代码中构建这样的脚本,然后每个父级执行一个请求。
请注意,如果已知子数据量很大,那么这可能不是一个好方法。我不知道细节,但我确信sql脚本的大小不会无限增长。