Using BULK INSERT to Load a Text File
By Garth Wells
on 19 March 2001
| 64 Comments
| Tags: bcp/Bulk Insert
This example combines dynamic SQL, BULK INSERT and the proper handling of double-quotes to solve a client's problem with loading various text file formats into a database. (This article has been updated through SQL Server 2005.)
One of my clients contacted
me recently and said they needed some help creating a stored
procedure that imported data from a text file. They wanted
the procedure to accept three parameters: PathFileName,
OrderID, and FileType. The PathFileName is simply the name
and physical location of the source file on the hard drive,
the OrderID is generated in the program that calls the procedure
and the FileType indicates the format of the data in the
source file. The two possible formats for the source data
are shown here:
I decided to use BULK INSERT to implement the solution.
The BULK INSERT statement was introduced in SQL Server 7
and allows you to interact with bcp (bulk copy program)
via a script. In pre-7 versions the only way you could access
bcp functionality was from a command prompt. I am not going
to list the full syntax of BULK INSERT here (but you can
here), because it is a little long and most of it does
not apply to the problem I am solving. Instead, I will show
the valid BULK INSERT statements used to load the data shown
BULK INSERT TmpStList FROM 'c:\TxtFile1.txt' WITH (FIELDTERMINATOR = '","')
TmpStList is the target table and TxtFile1.txt is the
source data file. The source file is located in the root
of the C drive. The FIELDTERMINATOR argument allows you
to specify the delimeter used to discern column values.
The valid statement for FileType=2 is shown here:
BULK INSERT tmpStList FROM 'c:\TxtFile2.txt' WITH (FIELDTERMINATOR = ',')
The only difference is the value of the FIELDTERMINATOR
The stored procedure used to implement the solution is
fairly straight forward once you master the BULK INSERT statement.
The only real trick is loading the data that comes to you
in FileType=1 format. Because a double-quote starts and
ends a data row, it too is loaded in the table. The FIELDTERMINATOR
works between columns, not at the beginning or
end of a row. To workaround this I simply load the data
into a temporary table and then use a CASE statement and
the SUBSTRING and DATALENGTH functions to load the correct
data in the final table. The FileType=2 data will load as-is,
but I still put in the temporary table for consistency (easier
The SQL statements that create the temporary and final
table are shown here.
CREATE TABLE StudentList
StID int IDENTITY NOT NULL,
StFName varchar(50) NOT NULL,
StLName varchar(50) NOT NULL,
StEmail varchar(100) NOT NULL,
OrderID int NOT NULL
CREATE TABLE TmpStList
stFName varchar (50) NOT NULL,
stLName varchar (50) NOT NULL,
stEmail varchar (100) NOT NULL
The procedure used to implement the data loading is shown
SET QUOTED_IDENTIFIER OFF
CREATE PROCEDURE ps_StudentList_Import
--Step 1: Build Valid BULK INSERT Statement
DECLARE @SQL varchar(2000)
IF @FileType = 1
-- Valid format: "John","Smith","firstname.lastname@example.org"
SET @SQL = "BULK INSERT TmpStList FROM '"+@PathFileName+"' WITH (FIELDTERMINATOR = '"",""') "
-- Valid format: John,Smith,email@example.com
SET @SQL = "BULK INSERT TmpStList FROM '"+@PathFileName+"' WITH (FIELDTERMINATOR = ',') "
--Step 2: Execute BULK INSERT statement
--Step 3: INSERT data into final table
INSERT StudentList (StFName,StLName,StEmail,OrderID)
SELECT CASE WHEN @FileType = 1 THEN SUBSTRING(StFName,2,DATALENGTH(StFName)-1)
CASE WHEN @FileType = 1 THEN SUBSTRING(StEmail,1,DATALENGTH(StEmail)-1)
--Step 4: Empty temporary table
TRUNCATE TABLE TmpStList
The first thing you need to know is that SET QUOTED_IDENTIFIER
is set to OFF because double-quotes are used to set the
value of a variable. Dynamic SQL is used to create the BULK
INSERT statement on-the-fly, and double-quotes are required
to do this. The final BULK INSERT statement is
a function of both the @PathFileName and @FileType parameters.
Once built, it is executed with the EXEC() statement and
the source data is loaded into the temporary table.
Once the data is in TmpStList, the next step is to load
it into the final table. I use the CASE statement to determine
the value in the @FileType parameter and manipulate accordingly.
When @FileType=1, the SUBSTRING and DATALENGTH functions
are used to remove the double-quotes from the StFName and
StEmail columns. When FileType=2, the data is loaded as
is and no manipulation is required.
After the data is loaded I empty the temporary table
with the TRUNCATE TABLE statement. I could have used DELETE
to accomplish this, but TRUNCATE TABLE has less of an impact
on the transaction log.
The following shows the way to call the procedure specifying
a different FileType value for each call.
EXEC ps_StudentList_Import 'c:\TxtFile1.txt',1, 1
EXEC ps_StudentList_Import 'c:\TxtFile2.txt',1, 2