Using Bulk insert from text file

In this blog we will see how we use bulk insert statement from SQL 2005 to insert large number of records from a 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.