SqlBulkCopy into composite primary key table

.net c# sql sqlbulkcopy sql-server-2005

Question

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.

1
4
10/9/2009 9:06:52 PM

Accepted Answer

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

1
10/9/2009 9:24:41 PM

Popular Answer

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.



Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow