Using Bulk insert from text file

As the title suggested we are going to see how do we insert bulk number of records from a text file to a SQL 2005 table. The technique is we should have the records in the text file in such a way that a columns and rows of records are terminated by unique letters. We call these letters as Field terminator and row terminator. We will experiment this with a quick walk through.

1) Connect to the NorthWnd database. (Don't know what is NorthWnd db? Google it)

2) Then Query the product table as shown below:

3) Next, Click on the No Column name to select the entire row under this single column

4) Then, Right click and copy the selected rows and paste it to a notepad.

5) Save it as Products~.txt in D:\

The text file now has the data for the bulk insert explanation. Note that the field terminator is ‘~' and row terminator is new line character ‘\n'. Now we will create table that will consume the data from this text file.

6) Create a table as shown below in the NorthWnd DB

Create table BLKInsertTest(ProdId int, Prodname varchar(40), UnitMeasure varchar(21));

7) Run the below Query to insert the data from the text file to the table created in the previous step

1: Specifies the name of the file from which we are going to pull the data for the table BLKInsertTest

2: Field terminator character that tell the where column ends in a text file for each row

3: Row terminator tells what is the row terminator. In our example new line is the row terminator

Note that executing the query inserts 77 rows at once.

1 Comment

Join the conversation! Your thoughts help the community grow.

Zoran Horvat13y ago
Hi Sivaraman, have you considered using SqlBulkCopy class from C# code for this task? Please take a look at my article which inserts a million parent-child records per minute with ID values recollection, without relying on BULK INSERT statement and external files: http://www.sysexpand.com/?path=howto/bulk-insert