Importing data from text file to specific columns using BULK INSERT

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.




Related Articles :

3 Responses to “Importing data from text file to specific columns using BULK INSERT”

  1. Should the view be modified if there’s a comma in the FirstName or LastName fields? How should that be handled?

  2. Commas are so common in text fields. I prefer to use pipe | as a delimiter. Line 5 of the bulk insert would become:

    FIELDTERMINATOR = ‘|’,

  3. SSIS actually uses BULK INSERT to load data, if the Fast Load option is selected.

    So the “Bulk insert is much faster …” statement is misleading, as it implies that different mechanisms are involved when using BULK INSERT.

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |