SqlBulkCopy insert with Identity column

Question

I use the SqlBulkCopy object to insert a couple of millions of generated rows into the database. The only problem is that the table I'm pasting into has an identifier column. I tried setting SqlBulkCopyOptions to SqlBulkCopyOptions.KeepIdentity and setting the identifier column 0 ', DbNull.Value and null . None of them worked. I feel like I am missing something quite simple if someone can enlighten me, which would be fantastic. Thank!

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

edit 2 Here is the code that 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); } 

Answer 1

For the destination table to assign an identifier, do not use the SqlBulkCopyOptions.KeepIdentity parameter. Instead, do not match the identity with the source and do not extract it from the source to send to SqlBulkCopy .

Answer 2

Fill in the ColumnMapping (see this ) of the BulkCopy object and do not match the identifier column. The identifier column will be created by the target database.

Answer 3

You have two options -

1 - use KeepIdentity and keep the original Identity values.

2 - Do not map the Identity field. If you are not trying to assign a value, the target table will assign it automatically.

Answer 4

Yes, you are right, using the SqlBulkCopyOptions.KeepIdentity parameter, then the writer with a volume copy does not think that what you have in the table structure, this object writes from the start column, so for our need I do the same to save the field of identification in my table, only you need to make an additional column in your datatable object with the remaining necessary columns and pass null values ​​to this column, then the table automatically processes Identity.

Answer 5

Reason : - In excel, there were several empty lines at the end of the data, which might look like empty lines. Bulk loading attempted to load these empty rows into a table.

Solution : - select only the rows containing the data - copy the data to a new sheet. Say that you have data in "Sheet 1", move it to "Sheet 2" and delete "Sheet 1".

Answer 6

In my case, it turned out to be an empty space inside the column name, and in one of the columns I accidentally used hyphon (-) instead of underscore (_) in my SQL table. I replaced the empty space and the hyphon with the underscore in the sql table and fixed the problem.