Importing data from text file to specific columns using BULK INSERT
- by Dinesh Asanka
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.
