SqlBulkCopy failing with String conversion issue

Question

I have a view which generates a number of columns which I want to bulk load into another table which has identically named columns.

This procedure worked fine when I was looping over the SqlDataReader and doing an insert with SqlParameters using a SqlCommand each time. Obviously for many 100000s of rows, this was too slow.

I switched to using SqlBulkCopy, as it seemed this would work since the views column names and the target db tables column names and types match (since the above procedure worked!).

However, on the first record it fails with an InvalidOperationException saying "The given value of type String cannot be converted to type smalldatetime of the specified target column". There is just one date column in this first row and its value is NULL.

The view is only returning a subset of columns, but the ones it is not are all nullable.

Any help would be appreciated.

Answer 1

Why do you nout just use a single sql statement

INSERT INTO YourTable (Col1, Col2,...) SELECT Col1, Col2, .. FROM YourView

If the view has the same columns as the table, you can drop the (Col1, Col2,...) after YourTable and change the statement to

INSERT INTO YourTable SELECT Col1, Col2, .. FROM YourView

But in general it is good form to specify the columns you are inserting into.

You can then also apply a where clause to the select from YourView.

Answer 2

try setting the SET DATEFORMAT to correct format (ymd, dmy, mdy, ...) for your connection and retry. this error usually happens if you have dates in a localized format and not locale independent format.

as for doing it all in one insert this is not recommended because this is considered a long running transaction and not a bulk insert operation.

Answer 3

sqlbulkcopy does not automatically map between columns with the same name. It assumes that your select will return columns in the same order it sees them in the destination table.

So if there are 7 columns in the destination table and the view returns 5 it will try and put those 5 in the the first 5 columns of the destination table.

The SqlBulkCopy class can take a collection of column mappings. That should do the trick