SqlBulkCopy的错误处理/继续错误 [英] SqlBulkCopy Error handling / continue on error

问题描述

我试图插入大量的数据到SQL Server。我的目标表中有一个名为哈希的唯一索引。

我想取代我的SqlDataAdapter执行与SqlBulkCopy的。在SqlDataAapter有一个叫ContinueUpdateOnError的属性，当设置为true adapter.Update（表）将插入的所有行可能并标记错误行与RowError属性。

现在的问题是我如何使用SqlBulkCopy的插入数据尽可能快地同时跟踪其行得到了插入的行没有（因唯一索引）？

在此先感谢！

下面是附加信息：

1. 这个过程是迭代的，往往按计划设置为重复。

2. 源和目标表可有时数百万行巨大的。

3. 即使它是可能的，以检查散列值第一，它需要每行两个事务（第一，用于选择从目标表中的散列，然后执行插入）。我觉得在adapter.update（表）的情况下，它是更快，以检查RowError不是检查每行散列命中。

解决方案

SqlBulkCopy的，具有非常有限的错误处理设施的，默认情况下它甚至不检查约束。

不过，它的速度快，真的很快。

如果你想解决重复的关键问题，并确定哪些行是批量复制。一种选择是：

• 在开始移植
• 抢在桌子上TABLOCKX选择当前所有哈希的价值观和Chuck他们在一个HashSet的。
• 过滤掉重复的和报告。
• 插入数据
• 提交移植

这个过程将有效地工作，如果要插入巨大的集合，并在表中的初始数据的大小是不是过于庞大。

能否请你扩大你的问题，包括问题的背景下休息。

修改

现在，我有一些更多的上下文这里是另一种方式，你可以去一下：

• 请批量插入到一个临时表。
• 在启动序列化的移植
• 选择他们所有的临时行已经存在于目标表...报告
• 将在临时表中的数据到真正的表，进行左连接上散列并包括所有的新行。
• 提交TRAN

这个过程是很轻的往返，并考虑您的规格应该结束了是非常快;

I am trying to insert huge amount of data into SQL server. My destination table has an unique index called "Hash".

I would like to replace my SqlDataAdapter implementation with SqlBulkCopy. In SqlDataAapter there is a property called "ContinueUpdateOnError", when set to true adapter.Update(table) will insert all the rows possible and tag the error rows with RowError property.

The question is how can I use SqlBulkCopy to insert data as quickly as possible while keeping track of which rows got inserted and which rows did not (due to the unique index)?

Thanks in advance!

Here is the additional information:

1. The process is iterative, often set on a schedule to repeat.

2. The source and destination tables can be huge, sometimes millions of rows.

3. Even though it is possible to check for the hash values first, it requires two transactions per row (first for selecting the hash from destination table, then perform the insertion). I think in the adapter.update(table)'s case, it is faster to check for the RowError than checking for hash hits per row.

解决方案

SqlBulkCopy, has very limited error handling facilities, by default it doesn't even check constraints.

However, its fast, really really fast.

If you want to work around the duplicate key issue, and identify which rows are duplicates in a batch. One option is:

• start tran
• Grab a tablockx on the table select all current "Hash" values and chuck them in a HashSet.
• Filter out the duplicates and report.
• Insert the data
• commit tran

This process will work effectively if you are inserting huge sets and the size of the initial data in the table is not too huge.

Can you please expand your question to include the rest of the context of the problem.

EDIT

Now that I have some more context here is another way you can go about it:

• Do the bulk insert into a temp table.
• start serializable tran
• Select all temp rows that are already in the destination table ... report on them
• Insert the data in the temp table into the real table, performing a left join on hash and including all the new rows.
• commit the tran

That process is very light on round trips, and considering your specs should end up being really fast;

这篇关于SqlBulkCopy的错误处理/继续错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！