Use BULK INSERT to load text files into SQL Server Database

by Manzoor Siddiqui · Published July 12, 2015 · Updated September 24, 2015

If you have a requirement to import large size text file into SQL Server database then you can use BULK INSERT statement. In previous articles we have seen Bulk Copy Program (BCP) utility, BULK INSERT is also similar to BCP except that this is operated within SQL Server command batch and files are accessed and handled by database server rather than using windows utility. BULK INSERT also provides options for CHECK_CONSTRAINTS and FIRE_TRIGGERS so that you can easily enable check constraints and triggers while importing data to tables to avoid duplicate records.

Below is an example to to load data from text file to database table where field terminator is comma (,) and row terminator is semi colon (;). You can change it according to your business requirement whether you are using pipe (|) or any other terminators.

BULK INSERT Inventory.dbo.tbl_NewVendorData
FROM 'C:\BulkCopy\ClientDataFile.txt'
WITH
 (
 FIELDTERMINATOR = ',',
 ROWTERMINATOR = ';\n'
 )
GO

Prior to executing above command, you should have respective table structure created in database to load the file according to data available in the text file with proper columns.

You need following permissions to access BULK INSERT feature.

– INSERT permission.

– ALTER TABLE permission

– ADMINISTER BULK OPERATIONS permission.