带有父/子关系的SQL批量插入,是否保留了订单?

c# sqlbulkcopy sql-server-2008

与下面提到的其他问题类似,我有两个表格结构:

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 (似乎)。

在使用带有INSERT SqlCommand 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对于我正在插入的那组父记录是唯一的。它适用于:

  1. 批量插入父记录与SqlBulkCopy ,让插入像往常一样自动生成recno标识字段。
  2. 仅对插入的记录执行SELECT

    select recno from parent where groupCode = @thisgroup order by recno;
    
  3. 使用检索到的值填充内存parentrecno记录的parentrecno字段

  4. 批量使用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 / parentrecno值不变。

然后,您可以使用合并输出从登台表中移动数据。

-- 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;

这只适用于SQL Server 2008(后来我推测)。


热门答案

这不是绝对的批量插入,而是它与父数据同时插入所有子数据,只对数据库进行一次往返。

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脚本的大小不能无限增长。




许可下: CC-BY-SA with attribution
不隶属于 Stack Overflow
这个KB合法吗? 是的,了解原因
许可下: CC-BY-SA with attribution
不隶属于 Stack Overflow
这个KB合法吗? 是的,了解原因