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:

  Pic05.JPG

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

 Pic06.JPG

 

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.