如何$被插入与SqlBulkCopy的p $ pvent重复记录时，有没有主键 [英] How to prevent duplicate records being inserted with SqlBulkCopy when there is no primary key

问题描述

我收到一封包含几千条记录每天的XML文件中，每个作为一个商业交易，我需要在报告和计费使用一个内部数据库来存储。
我每一天的文件只包含唯一的记录IM pression之下，但已经发现，我的独特的定义是不完全一样的提供商。

I receive a daily XML file that contains thousands of records, each being a business transaction that I need to store in an internal database for use in reporting and billing. I was under the impression that each day's file contained only unique records, but have discovered that my definition of unique is not exactly the same as the provider's.

这是进口该数据当前应用程序是一个C＃.NET 3.5的控制台应用程序，它并因此使用SqlBulkCopy的成MS SQL Server 2008数据库表，其中列的XML记录的结构完全匹配。每个记录有刚刚超过100场，并没有在数据中没有自然键，或者我可以拿出决策意识的复合键落得也不必允许空值，而该领域。目前该表有多个索引，但没有主键。

The current application that imports this data is a C#.Net 3.5 console application, it does so using SqlBulkCopy into a MS SQL Server 2008 database table where the columns exactly match the structure of the XML records. Each record has just over 100 fields, and there is no natural key in the data, or rather the fields I can come up with making sense as a composite key end up also having to allow nulls. Currently the table has several indexes, but no primary key.

基本上整个行需要是唯一的。如果一个场是不同的，它是足够的有效插入。我看着创建整行的MD5哈希值，即插入到数据库中，并用约束prevent使用SqlBulkCopy从插入行，但我看不出如何让MD5哈希到BulkCopy运行和我也不太清楚，如果整个操作将失败并回滚，如果任何一个失败的记录，或者如果它会继续下去。

Basically the entire row needs to be unique. If one field is different, it is valid enough to be inserted. I looked at creating an MD5 hash of the entire row, inserting that into the database and using a constraint to prevent SqlBulkCopy from inserting the row,but I don't see how to get the MD5 Hash into the BulkCopy operation and I'm not sure if the whole operation would fail and roll back if any one record failed, or if it would continue.

该文件包含了非常大量的记录，在XML再逐行，查询数据库，将所有字段记录，然后决定插入真的，我可以看到能够做到这一点的唯一方法。我只是希望不要有完全重写应用程序，以及大容量复制操作是如此之快。

The file contains a very large number of records, going row by row in the XML, querying the database for a record that matches all fields, and then deciding to insert is really the only way I can see being able to do this. I was just hoping not to have to rewrite the application entirely, and the bulk copy operation is so much faster.

有谁知道的方式使用SqlBulkCopy的同时preventing重复行，没有一个主键？或任何建议对不同的方式做到这一点？

Does anyone know of a way to use SqlBulkCopy while preventing duplicate rows, without a primary key? Or any suggestion for a different way to do this?

推荐答案

我把数据上传到一个临时表，然后用重复事后处理上拷贝到决赛桌。

I'd upload the data into a staging table then deal with duplicates afterwards on copy to the final table.

例如，你可以创建临时表（非唯一）的索引来处理的钥匙

For example, you can create a (non-unique) index on the staging table to deal with the "key"

这篇关于如何$被插入与SqlBulkCopy的p $ pvent重复记录时，有没有主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！