SqlBulkCopy Insert with Identity Column

c# identity-insert sqlbulkcopy sql-server

Question

I am using the SqlBulkCopy object to insert a couple million generated rows into a database. The only problem is that the table I am inserting to has an identity column. I have tried setting the SqlBulkCopyOptions to SqlBulkCopyOptions.KeepIdentity and setting the identity column to 0's, DbNull.Value and null. None of which have worked. I feel like I am missing something pretty simple, if someone could enlighten me that would be fantastic. Thanks!

edit To clarify, I do not have the identity values set in the DataTable I am importing. I want them to be generated as part of the import.

edit 2 Here is the code I use to create the base SqlBulkCopy object.

SqlBulkCopy sbc = GetBulkCopy(SqlBulkCopyOptions.KeepIdentity);
sbc.DestinationTableName = LOOKUP_TABLE;

private static SqlBulkCopy GetBulkCopy(SqlBulkCopyOptions options = 
    SqlBulkCopyOptions.Default) 
{
    Configuration cfg = WebConfigurationManager.OpenWebConfiguration("/RSWifi");
    string connString =
    cfg.ConnectionStrings.ConnectionStrings["WifiData"].ConnectionString;
    return new SqlBulkCopy(connString, options);
}

Accepted Answer

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.


Popular Answer

Fill the ColumnMapping of the BulkCopy object and don't map the identity column. The identity column will be generated by the target database.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why