SqlBulkCopy into table with composite primary key

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.

Answer 1

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

Answer 2

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.

Answer 3

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.