Bulk Insert to read delimited file in SQL

Bulk insert is very simple command to use in SQL server to import the data.

Below is the syntax for the command taken from Microsoft SQL Server online help.

Syntax:

BULK INSERT

   [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ] 
      FROM 'data_file' 
     [ WITH 
    ( 
   [ [ , ] BATCHSIZE = batch_size ] 
   [ [ , ] CHECK_CONSTRAINTS ] 
   [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] 
   [ [ , ] DATAFILETYPE = 
      { 'char' | 'native'| 'widechar' | 'widenative' } ] 
   [ [ , ] FIELDTERMINATOR = 'field_terminator' ] 
   [ [ , ] FIRSTROW = first_row ] 
   [ [ , ] FIRE_TRIGGERS ] 
   [ [ , ] FORMATFILE = 'format_file_path' ] 
   [ [ , ] KEEPIDENTITY ] 
   [ [ , ] KEEPNULLS ] 
   [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ] 
   [ [ , ] LASTROW = last_row ] 
   [ [ , ] MAXERRORS = max_errors ] 
   [ [ , ] ORDER ( { COLUMN [ ASC | DESC ] } [ ,...n ] ) ] 
   [ [ , ] ROWS_PER_BATCH = rows_per_batch ] 
   [ [ , ] ROWTERMINATOR = 'row_terminator' ] 
   [ [ , ] TABLOCK ] 
   [ [ , ] ERRORFILE = 'file_name' ] 
    )]

You can study about the different parameters for this command on Microsoft site, for now we will use simple text file which is pipe delimited for fields and new line to separate the records.

Steps:

1) Create a text file with below data and named as “DataFile.txt”

1|Abc|Address1|Email1|Designation1
2|Pqr|Address2|Email2|Designation2

2) Create a temporary table to store the data from file using Bulk Insert command.

CREATE TABLE TempTable(
EmpID int,
EmpName varchar(100),
EmpAdd varchar(100),
EmpEmail varchar(50),
EmpDesignation varchar(100)
)
Go

3)  Prepare and execute the SQL command to import the data from file.

Declare @SQL varchar(max)

Set @SQL =
‘BULK INSERT TempTable
FROM ”D:\DataFile.txt”
WITH
(
FIELDTERMINATOR = ”|”,
ROWTERMINATOR = ”’ + char(10) +”’
);’

execute (@SQL)
Go

4) Verify the data using

Select * From TempTable

Output should look like

BulkInsert

This is a very simple example to import the data using bulk insert command.  You can do the complex task using bulk insert, for that you can read the articles on Microsoft site for detail of different attributes available for this command.

 

 

 

Advertisements

#bulk-insert