SqlBulkCopy 到具有复合主键的表中
我正在尝试使用 SqlBulkCopy 通过在我的应用程序中手动填充 DataTable 来将新行插入到我的数据库表中。
这适用于所有表除了具有由 3 列组成的复合主键的表。每当我尝试将 SqlBulkCopy 任何内容放入此表中时，都会收到以下错误：
Violation of PRIMARY KEY constraint 'PK_MYCOMPOSITEKEY'. Cannot insert duplicate key in object 'dbo.MyTable'.
The statement has been terminated.
这可能吗？
我尝试使用以下内容设置数据表的主键：
dt.PrimaryKey = new[] {dt.Columns["PKcolumn1"], dt.Columns["PKcolumn2"], dt.Columns["PKcolumn3"]};
但同样，没有运气。
I'm trying to use SqlBulkCopy to insert new rows into my DB table by manually populating a DataTable w/in my application.
This works fine for all tables except the table that has a composite primary key made up of 3 columns. Whenever I try to SqlBulkCopy anything into this table, I get the following error:
Violation of PRIMARY KEY constraint 'PK_MYCOMPOSITEKEY'. Cannot insert duplicate key in object 'dbo.MyTable'.
The statement has been terminated.
Is this even possible?
I have tried setting up my DataTable's primary keys with the following:
dt.PrimaryKey = new[] {dt.Columns["PKcolumn1"], dt.Columns["PKcolumn2"], dt.Columns["PKcolumn3"]};
but again, no luck.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您遇到的问题是数据。
在输入文件中，有一行或两行的
e pk 列中的数据与表中已有的数据相同
，或者
该文件至少有两行的 pk 列值相同
The problem you have is with the data.
In the input file there is either or both of
a row which has the same data in the e pk columns as you already have in the table
or
The file has at least two rows with the same values of the pk columns
批量插入到暂存表。清理所有重复记录。然后使用直接 SQL 进行插入。当您编写插入代码时，请确保将其限制为暂存表中不在产品表中的记录。
Bulk insert to a staging table. Clean up any duplicate records. Then do an insert using straight SQL. When you write the insert code be sure to limit it to records in the staging table that are not in the prod table.
您应该在访问数据库之前验证批量数据的副本，问题也可能存在（不仅仅是与现有约束或数据库中的记录冲突）。它确实有效，并且报告它通常是正确的。
尽管如此，DataSet 甚至 DataReaders 的整个展示在映射、糟糕的无类型设计、大量不必要的转换、分配、基于 object[] 的值方面都是一个混乱的练习，并且整个事情变得依赖于顺序、类型和字符串的混乱（只有 MS 的东西）可以设计并持续设计）。另一方面，本机 OLEDB 批量接口要干净得多。
You should verify your bulk data for copies before you hit the DB, the problem could be there as well (not just clashing with an existing constraint, or record in DB). It does work and it is usually correct to report it.
Nonetheless, the entire show of DataSet or even DataReaders is a messy exercise in mappings, bad typeless design, plenty of unnecessary transformations, allocations, object[] based values, and the entire thing becomes order, type and string dependent mess (something only MS could design and keeps designing). Native OLEDB bulk interfaces on the other hand are much cleaner.