0

I have a SQL Server table where the 1st column ID is primary identity column, 2nd column is BatchId which is also part of the primary key.

In datatable dt, I am adding column batchId explicitly. When inserting from the datatable to SQL Server using SqlBulkCopy, I am assigning same value to batchId column (for e.g. for today's date batchId will be 1, for next date batchId will be 2 and so on), but SqlBulkCopy states error

Cannot insert null values to column BatchId from table 'xxxx'";

despite assigning values. I have also passed the SqlBulkCopyOptions.KeepIdentity & SqlBulkCopyOptions.KeepNulls parameters to SqlBulkCopy class.

Any help appreciated, thanks in advance!

pnuts
  • 58,317
  • 11
  • 87
  • 139
bhushanMvc
  • 1
  • 1
    Can you share some code? – Ako Mar 20 '15 at 06:48
  • As far as I know, `SqlBulkCopy` will always try to insert values into all columns of the target table. So in your case, this won't work - since you don't want to provide a value for the `ID` identity column. Solution: you need to bulk-load your data into a **staging** table that has the exact same structure as your `DataTable`, and then do an insert from there into the actual, main table on SQL Server (specifying to omit the `ID` column in your `INSERT` statement) – marc_s Mar 20 '15 at 06:57
  • Duplicate - http://stackoverflow.com/questions/6651809/sqlbulkcopy-insert-with-identity-column – Yosi Dahari Aug 25 '15 at 17:38

0 Answers0