Bulk insert is much faster than using other techniques such as SSIS. However, when you are using bulk insert you can’t insert to specific columns. If, for example, there are five columns in a table you should have five values for each record in the text file you are importing from.
This is an issue when you are expecting default values to be inserted into tables.
Let us say you have table as below:
In this table, you are expecting ID, Status and CreatedDate to be updated automatically, so your text file may only have FirstName LastName values as below:
Dinesh,Asanka
Saman,Liyanage
Ruwan,Silva
Susantha,Bathige
Jude,Peires
Sanjeewa,Jayawickrama
If you use bulk insert to this table like follows,
You will be returned an error:
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (ID).
To avoid this you will need to create a view with the columns you are expecting to fill and use bulk insert against it.
If you check the table now, you will see table with values in the text file and the default values.
