标识列上具有父子关系的SqlBulkCopy和DataTables

首先:SqlBulkCopy不可能做您想要的事情。顾名思义,这只是一条“单行道”。我会尽快将数据移到sql server中。它是旧的大容量复制命令的.Net版本,用于将原始文本文件导入表中。因此,如果使用SqlBulkCopy,就无法获取标识值。

我做了很多大量的数据处理,并且多次遇到这个问题。解决方案取决于您的体系结构和数据分发。以下是一些想法:

• 为每个线程创建一组目标表,导入到这些表中。最后把这些桌子连起来。其中大部分可以以一种非常通用的方式实现,即从名为TABLENAME的表自动生成名为TABLENAME_THREAD_ID的表。

• 将ID生成完全移出数据库。例如,实现一个生成id的中心webservice。在这种情况下,不应该为每个调用生成一个ID,而是生成ID范围。否则,网络开销通常会成为一个瓶颈。

• 试着把你的数据生成id。如果有可能的话,你的问题就解决了。不要说“不可能”要快。也许您可以使用字符串id,它可以在后处理步骤中清除?

还有一句话要说:在使用BulkCopy时,增加了34个因子,这听起来很小。如果要快速插入数据,请确保数据库配置正确。

读这篇文章。我想这正是你想要的。非常好和优雅的解决方案。

http://www.codinghelmet.com/?path=howto/bulk-insert

使用SqlBulkCopy可以完成所需操作的唯一方法是首先将数据插入到临时表中。然后使用存储过程将数据分发到目标表。是的,这将导致经济放缓,但速度仍将很快。

你也可以考虑重新设计你的数据,比如把它拆分,去规格化等等。

set identity_insert <table> on 和 dbcc checkident 你的朋友在这里吗。这与我过去做的类似(参见代码示例)。唯一真正需要注意的是,更新过程是唯一可以插入数据的过程:在进行更新时,其他所有人都必须离开池。当然,您可以在加载生产表之前以编程方式进行这种映射。但对插入也有同样的限制:更新进程是唯一可以播放的进程。

--
-- start with a source schema -- doesn't actually need to be SQL tables
-- but from the standpoint of demonstration, it makes it easier
--
create table source.parent
(
  id   int         not null primary key ,
  data varchar(32) not null ,
)
create table source.child
(
  id        int         not null primary key ,
  data      varchar(32) not null ,
  parent_id int         not null foreign key references source.parent(id) ,
)

--
-- On the receiving end, you need to create staging tables.
-- You'll notice that while there are primary keys defined,
-- there are no foreign key constraints. Depending on the
-- cleanliness of your data, you might even get rid of the
-- primary key definitions (though you'll need to add
-- some sort of processing to clean the data one way or
-- another, obviously).
--
-- and, depending context, these could even be temp tables
--
create table stage.parent
(
  id   int         not null primary key ,
  data varchar(32) not null ,
)

create table stage.child
(
  id        int         not null primary key ,
  data      varchar(32) not null ,
  parent_id int         not null ,
)

--
-- and of course, the final destination tables already exist,
-- complete with identity properties, etc.
--
create table dbo.parent
(
  id int not null identity(1,1) primary key ,
  data varchar(32) not null ,
)
create table dbo.child
(
  id int not null identity(1,1) primary key ,
  data varchar(32) not null ,
  parent_id int not null foreign key references dbo.parent(id) ,
)

-----------------------------------------------------------------------
-- so, you BCP or otherwise load your staging tables with the new data
-- frome the source tables. How this happens is left as an exercise for
-- the reader. We'll just assume that some sort of magic happens to
-- make it so. Don't forget to truncate the staging tables prior to
-- loading them with data.
-----------------------------------------------------------------------

-------------------------------------------------------------------------
-- Now we get to work to populate the production tables with the new data
--
-- First we need a map to let us create the new identity values.
-------------------------------------------------------------------------
drop table #parent_map
create table #parent_map
(
  old_id int not null primary key nonclustered       ,
  offset int not null identity(1,1) unique clustered ,
  new_id int     null ,  
)
create table #child_map
(
  old_id int not null primary key nonclustered ,
  offset int not null identity(1,1) unique clustered ,
  new_id int     null ,
)

insert #parent_map ( old_id ) select id from stage.parent
insert #child_map  ( old_id ) select id from stage.child

-------------------------------------------------------------------------------
-- now that we've got the map, we can blast the data into the production tables
-------------------------------------------------------------------------------

--
-- compute the new ID values
--
update #parent_map set new_id = offset + ( select max(id) from dbo.parent )

--
-- blast it into the parent table, turning on identity_insert
--
set identity_insert dbo.parent on

insert dbo.parent (id,data)
select id   = map.new_id   ,
       data = staging.data
from stage.parent staging
join #parent_map  map     on map.old_id = staging.id

set identity_insert dbo.parent off

--
-- reseed the identity properties high water mark
--
dbcc checkident dbo.parent , reseed


--
-- compute the new ID values
--
update #child_map set new_id = offset + ( select max(id) from dbo.child )

--
-- blast it into the child table, turning on identity_insert
--
set identity_insert dbo.child on

insert dbo.child ( id , data , parent_id )
select id        = parent.new_id   ,
       data      = staging.data    ,
       parent_id = parent.new_id

from stage.child staging
join #child_map  map      on map.old_id    = staging.id
join #parent_map parent   on parent.old_id = staging.parent_id

set identity_insert dbo.child off

--
-- reseed the identity properties high water mark
--
dbcc checkident dbo.child , reseed

------------------------------------
-- That's about all there is too it.
------------------------------------

我想你面临的取舍是BulkInsert的性能和身份的可靠性。

是否可以将数据库暂时置于SingleUserMode以执行插入操作?

在我的转换项目中,我遇到了一个非常类似的问题,我在非常大的表中添加了一个Identity列,它们有子表。幸运的是,我能够设置父和子源的标识(我使用了一个TextDataReader)来执行BulkInsert,同时生成父和子文件。

我还获得了您所说的性能提升,OleDBDataReader Source->StreamWriter。。。然后文本数据读取器->SQLBulk