Sqlbulkcopy Insert with Identity Column

SqlBulkCopy Insert with Identity Column

To have the destination table assign the identity, DO NOT use the SqlBulkCopyOptions.KeepIdentity option. Instead, don't map the identity from the source, and don't extract it from source to send through to SqlBulkCopy.

Bulk insert with destination table identity column

You need to remove the SqlBulkCopyOptions.KeepIdentity option. You are telling it to use the identity that you provide and there is none, aka null.

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopyoptions%28v=vs.110%29.aspx

KeepIdentity

Preserve source identity values. When not specified, identity values are assigned by the destination.

SqlBulkCopy inserting in a table with Identity

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++;
}

Retrieving identity column after inserting data using SQLBulkCopy

SqlBulkCopy only handles the copying of data, and is not an advanced DB Layer. As such it does not take responsibility for pulling back the IDs, and can't actually do it if it wanted to.

A pattern followed by most is to use SqlBulkCopy to insert to a staging temporary table, and then using INSERT INTO..OUTPUT Inserted.Id..SELECT * FROM #Tmp to get those rows in to your main table.

This will allow you to pull back your inserted IDs and update your models.

SQLBulkCopy with Identity Insert in destination table

Finally I got this worked this way

    using (var bulkCopy = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString, SqlBulkCopyOptions.KeepNulls & SqlBulkCopyOptions.KeepIdentity))
                {
                    bulkCopy.BatchSize = (int)DetailLines;
                    bulkCopy.DestinationTableName = "dbo.myTable";
                    bulkCopy.ColumnMappings.Clear();
                    bulkCopy.ColumnMappings.Add("SourceColumnName", "DestinationColumnName");
                    bulkCopy.ColumnMappings.Add("SourceColumnName", "DestinationColumnName");
                    bulkCopy.ColumnMappings.Add("SourceColumnName", "DestinationColumnName");
                    bulkCopy.ColumnMappings.Add("SourceColumnName", "DestinationColumnName");
                    .
                    .
                    .
                    .
                    bulkCopy.ColumnMappings.Add("SourceColumnName", "DestinationColumnName");

                    bulkCopy.WriteToServer(datatable);
                }

SqlBulkCopy Identity on SQL SERVER

If your local table definition does not match the server's (it is missing the identity column), then you need to add an explicit mapping for each column (case-sensitive)

The first value is the name of the local source column, the second is destination column name.

bulk.ColumnMappings.Add("one", "one");
bulk.ColumnMappings.Add("two", "two");

This is also the case if the names don't match up exactly.

---

Related Topics