问题描述
我正在尝试将大量数据插入 SQL 服务器.我的目标表有一个名为Hash"的唯一索引.
I am trying to insert huge amount of data into SQL server. My destination table has an unique index called "Hash".
我想用 SqlBulkCopy 替换我的 SqlDataAdapter 实现.在 SqlDataAapter 中有一个名为ContinueUpdateOnError"的属性，当设置为 true 适配器时.Update(table) 将插入所有可能的行并使用 RowError 属性标记错误行.
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.
问题是如何使用 SqlBulkCopy 尽可能快地插入数据，同时跟踪哪些行被插入，哪些行没有被插入(由于唯一索引)?
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)?
这里是附加信息:
这个过程是迭代的，通常会按计划重复.
The process is iterative, often set on a schedule to repeat.
源表和目标表可能很大，有时有数百万行.
The source and destination tables can be huge, sometimes millions of rows.
尽管可以先检查散列值，但每行需要两个事务(首先从目标表中选择散列，然后执行插入).我认为在 adapter.update(table) 的情况下，检查 RowError 比检查每行的哈希命中要快.
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 的错误处理功能非常有限，默认情况下它甚至不检查约束.
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:
- 开始翻译
- 在桌子上抓一个 tabblockx 选择所有当前的哈希"值并将它们放入一个 HashSet 中.
- 过滤掉重复项并报告.
- 插入数据
- 提交翻译
如果您要插入大量集合并且表中初始数据的大小不是太大，则此过程将有效.
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.
编辑
现在我有了更多的上下文，这里是另一种方法:
Now that I have some more context here is another way you can go about it:
- 将批量插入临时表.
- 启动可序列化的传输
- 选择目标表中已经存在的所有临时行...报告它们
- 将临时表中的数据插入到真实表中，对散列执行左连接并包括所有新行.
- 提交翻译
这个过程在往返过程中非常轻松，考虑到您的规格最终应该非常快；
That process is very light on round trips, and considering your specs should end up being really fast;
这篇关于SqlBulkCopy 错误处理/错误继续的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！