SqlBulkCopy inserting in a table with Identity

Question

this is my scenario: I have to import huge files in a table, so I used sqlBulkCopy cause other ways are too slowly.

This is an example of a file row:

String1|String2|String3|String4

And this is the table structure:

Id (Identity,PK)|Column1|Column2|Column3|Column4

I split row by '|', I create dataTable where I put every column of table (Except Id) reading infos by a configuration file:

DataColumn dc;
foreach (Tables.BulkColumn bc in columns)
{
    dc = new DataColumn();
    dc.DataType = bc.ColumnValueType;
    dc.ColumnName = bc.Name;
    dc.Unique = false;
    actualDataTable.Columns.Add(dc);
}

I load data from file to dataTable (data here are correct), initialize SqlBulkCopy and writeToServer:

SqlBulkCopy sbc = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.TableLock);
sbc.BulkCopyTimeout = 600;
sbc.WriteToServer(actualDataTable);

But, when I look in database, this is the inserted row:

IncrementedId|String2|String3|String4

It doesn't insert the Column1 value, it seems to work with columns index, but initializing dataColumns I set name explicitly.

Do you know how to solve it? The Id has to be inserted automatically writing other values correctly.

(I try moving Id as the last columns, it works correctly in this way, but I don't like this solution)

UPDATE

To insert data from txt to table, I read table structure from my configuration, e.g

<table name="Table" file="D:\Progetti\TestArea\test\SqlBulkCopy\FP20150716003004.txt" incremental="false" active="true" identity="true">
      <field name="Column1" type="System.String" default="" key="false" allowsNull="true" length="50" />
 <!-- Other Fields --> ...

All fields are in the same order of the columns in input file. I split file rows and I put data into a DataRow:

dr[actualIndexColumnName] = string.IsNullOrEmpty(splitted[columnIndex]) ? tab.Columns[columnIndex].DefaultValue : splitted[columnIndex];

Where splitted[columnIndex] is actual input value, and when all columns of the row are filled I add this one to a DataTable

dt.Rows.Add(dr);

Possible duplicate of http://stackoverflow.com/questions/6651809/sqlbulkcopy-insert-with-identity-column. This question is essentially about how `SqlBulkCopy` deals with identity columns. (Set `SqlBulkCopyOptions.KeepIdentity = true`, perhaps?) — stakx - no longer contributing, Jul 21 '15 at 09:22
@stakx I tried Setting SqlBulkCopyOptions.KeepIdentity = true but it gives to me an System.FormatException cause it seems to work using indexes (Id is int, column1 is nvarchar) — SamDroid, Jul 21 '15 at 09:43
Perhaps you should stop and think about why `column1` is `nvarchar`. Shouldn't it be `int`, like in the actual database schema? If so, does that imply that perhaps you need to modify your code that builds the `DataTable` (i.e. add something like `int.Parse` after the `string.Split`)? — stakx - no longer contributing, Jul 21 '15 at 09:55
I edited my question changing values inserted in table to clarify that columns except Id are nvarchar — SamDroid, Jul 21 '15 at 10:03
**1.** It would help if you showed the code that converts your input to a `DataTable`. **2.** Re: _"when I look in database, this is the inserted row"_ - What does the `SELECT` statement look like? — stakx - no longer contributing, Jul 21 '15 at 10:12
Your `SELECT *` yields a result set with 4 columns, but your `DataTable` apparently has five columns. I understand that your `DataTable` has a `column1` column, but I guess your database table does not have the same column. (You don't explicitly describe the schema for your database table.) No wonder it doesn't insert the values from that column into the database... — stakx - no longer contributing, Jul 21 '15 at 10:25

score 2 · Answer 1 · answered Jul 21 '15 at 10:30

2

You are missing a column map for your SqlBulkCopy.

foreach (Tables.BulkColumn bc in columns)
{
   sbc.ColumnMapping.Add(bc.name);
}

answered Jul 21 '15 at 10:30

Remus Rusanu

288,378
40
442
569

Your solution works, but doesn't exist Add overloads that accepts only one parameter, so I used the overload that accept columnName, columnPosition. – SamDroid Jul 21 '15 at 10:50

score 1 · Accepted Answer · answered Jul 22 '15 at 08:13

I solved my problem using adding mappings to SqlBulkCopy in this way:

sbc.ColumnMappings.Clear();
int i = hasTableIdentity ? 1 : 0;
DataColumn dc;
foreach (Tables.BulkColumn bc in columns)
{
    dc = new DataColumn();
    dc.DataType = bc.ColumnValueType;
    dc.ColumnName = bc.Name;
    dc.Unique = false;
    sbc.ColumnMappings.Add(dc.ColumnName, i);
    actualDataTable.Columns.Add(dc);
    i++;
}

SqlBulkCopy inserting in a table with Identity

2 Answers2