Import bulk data from flat file to SQL table using BCP and BULK INSERT

There are four ways to populate the data from flat file to SQL table and each method we can run in different environment. In this post, we are going to see what are the types used to transfer the data from flat file to SQL table.

For instance, we have employee data flat file like as below and we need to populate the data on Employee data by different methods.

Employee_Id,Employee_Name,Employee_Address,Employee_City,Employee_State,Employee_Salary
1001,BaskaranR,"50, Mount Road",Chennai,TamilNadu,50000
1002,xxxxxxxxx,"16, Cross Street",Chennai,TamilNadu,45000
1003,yyyyyyyyy,"74, DSI estate",Chennai,TamilNadu,37000
1004,zzzzzzzzz,"125, Mount Road",Chennai,TamilNadu,50000
1005,vvvvvvvvv,"86, Cross Street",Chennai,TamilNadu,45000

BCP Utility

BCP stands for Bulk Copy Program utility which is used to import or export the large amount of data between flat file and SQL Server table with specified format. We can execute this utility either command prompt or SQL Server Management Studio (SSMS) with format file.

That format file can be .XML or .FMT file where we need to mention the sequence of columns name, data type and length.
Read more how to create or generate format file.

Execute BCP utility in Command Prompt

Below statement has basic parameters to execute the BCP utility and we can add more parameters based on the requirement.

// If the Server supports windows authentication
bcp database_name.dbo.Employee in "D:\DataLoad\Employee.txt" -F 2 -f "D:\DataLoad\bcpformat.fmt" -T

// If the Server supports SQL authentication user id and password
bcp database_name.dbo.Employee in "D:\DataLoad\Employee.txt" -F 2 -f "D:\DataLoad\bcpformat.fmt" –U sa –P password

Execute BCP utility in SQL Server Management Studio (SSMS)

Before executing BCP utility in SSMS, we need to enable the xp_cmdshell in server configuration which is used to call command prompt in SSMS. Read more how to enable the xp_cmdshell.

-- If the Server supports windows authentication
EXEC master..xp_cmdshell 'bcp database_name.dbo.Employee in "D:\DataLoad\Employee.txt" -F 2 -f "D:\DataLoad\bcpformat.fmt" -S SERVERNAME -T'

-- If the Server supports SQL authentication user id and password
EXEC master..xp_cmdshell 'bcp database_name.dbo.Employee in "D:\DataLoad\Employee.txt" -F 2 -f "D:\DataLoad\bcpformat.fmt" -S SERVERNAME –U sa –P password'

BULK INSERT Method

BULK INSERT is a method using this we can import bulk data from flat file to SQL table in SQL Server Management Studio (SSMS) using format file. Read more how to create format file.

BULK INSERT Database_name.dbo.Employee
FROM 'D:\DataLoad\Employee.txt'
WITH
(
 FIRSTROW = 2,
        FORMATFILE = 'D:\DataLoad\BulkInsertFormat.xml'
);

OPENROWSET Method

An another method to load the data from flat file to SQL table and additionally we can view the data before loading into SQL table. Read more how to create format file to import bulk data from flat file to SQL Server

INSERT INTO Employee
SELECT * FROM OPENROWSET( BULK 'D:\DataLoad\Employee.txt',
FORMATFILE = 'D:\DataLoad\BulkInsertFormat.xml',
FIRSTROW = 2) AS a;

Import and Export wizard in SQL Server

We have another option in SQL Server Management Studio (SSMS) to import the data from flat file to SQL table and the steps as follows.

Step 1: Right click on the database where we need to import and choose Tasks from the list and click Import Data option.

Step 2: In Choose a data source window,  choose Flat File Source from the data source list and select the file path using Browse button.

Step 3: Type double quotes (") on Text qualifier text box and preview the data choosing columns in left panel then click Next.

Step 4:  In Choose a Destination window, choose SQL Server Native Client 11.0 from the list and mention the SQL server and credential details then click Next.

Step 5: In Select Source Tables and Views window, Type the destination table or choose existing table.

Step 6: Click Edit Mappings button and verify the table columns, data types and length.

Step 7: Click Next and Finish button if all the columns mapped appropriately.

Also see below articles

Monitoring and Performance Tuning Tips for SQL Server database
SQL Server/Developer Interview Questions and Answers for Experienced Professional
How to generate format file for BULK INSERT and BCP with Text Qualifier in SQL Server