How To Import CSV File Using Bulk Insert In SQL Server

SQL Server’s process command BULK INSERT to load data from a user-specified file format into database tables. Specified file format can have any column & row terminator, for instance – pipe, space & comma. Furthermore, this tech-recipes post demonstrates how to import CSV files using BULK INSERT in SQL Server.

To enumerate other ways to load CSV files into SQL Server are using Import/Export wizard and SSIS.

Following is the basic syntax and related arguments to consider while loading data using BULK INSERT into SQL Server. Under this post, we are covering important BULK INSERT properties to get started. If you like to go in advance, you may refer to SQL Server BOL documentation.

BULK INSERT – SQL Server – Syntax

BULK INSERT <Table Name> 
FROM <File Name>
WITH 
    (
        [CHECK_CONSTRAINTS]
        [FIRSTROW]
        [KEEPIDENTITY]
	[KEEPNULLS]
	[ORDER]
	[FORMAT]
	[ROWTERMINATOR]
	[FIELDTERMINATOR]
    )
GO

Following the above syntax, we will demonstrate the BULK INSERT with various examples to understand how different properties work.

1.

Importing Simple CSV file using BULK INSERT

The following data will be loaded into dbo.Employee table using BULK INSERT. We do not have to specify any other properties considering the data is in the correct CSV format. Only specifying FORMAT=’CSV’ will import the data into table.

CSV File Data

E100,Atul,100,IT
E101,Chetan,200,HR
E102,Niraj,300,Legal
E103,Vishal,400,HR
E104,Sangram,500,Leadership
E105,Shailesh,600,Legal

Bulk Insert Query

DROP TABLE IF EXISTS dbo.Employee;

CREATE TABLE dbo.Employee
(
 EmployeeId      VARCHAR(100)
,Name            VARCHAR(255)  NULL
,Salary          INT            
,Dept            VARCHAR(100)  NULL
);

TRUNCATE TABLE dbo.Employee;

BULK INSERT dbo.Employee
FROM 'D:\EmployeeDetails.csv'
WITH (FORMAT='CSV');

SELECT * FROM dbo.Employee;

2.

Importing Data with FIELDTERMINATOR & ROWTERMINATOR using BULK INSERT

In the following text file we have pipe { | } as FIELDTERMINATORand comma { ; } as ROWTERMINATOR. Specifying these properties with BULK INSERT will import the data into the Employee table.

CSV File Data

E100|Atul|100|IT;
E101|Chetan|200|HR;
E102|Niraj|300|Legal;
E103|Vishal|400|HR;
E104|Sangram|500|Leadership;
E105|Shailesh|600|Legal;

Bulk Insert Query

TRUNCATE TABLE dbo.Employee;

BULK INSERT dbo.Employee
FROM 'D:\EmployeeDetails.csv'
WITH (
        FIELDTERMINATOR = '|'
       ,ROWTERMINATOR   = ';'
     );

SELECT * FROM dbo.Employee;

3.

Importing Data with KEEPNULLS & KEEPIDENTITY using BULK INSERT

We have modified the text file data & table structure to demonstrate this example. Added identity column as a primary key in the employee table so BULK INSERT should consider the values provided in the file for the IDENTITY column. KEEPIDENTITY prevents SQL Server from generating its own values and forces to consider from the file while importing.

KEEPNULLS property will insert NULL for employee name if not specified like for employee E104 in the following file. Ingoing KEEPNULLS property will add a blank value.

CSV File Data

1|E100|Atul|100|IT;
2|E101|Chetan|200|HR;
3|E102|Niraj|300|Legal;
6|E103|Vishal|400|HR;
7|E104||500|Leadership;
8|E105|Shailesh|600|Legal;

Bulk Insert Query

DROP TABLE IF EXISTS dbo.Employee;

CREATE TABLE dbo.Employee
(
 SerialNo        INT           IDENTITY(1,1)
,EmployeeId      VARCHAR(100)
,Name            VARCHAR(255)  NULL
,Salary          INT            
,Dept            VARCHAR(100)  NULL
);

BULK INSERT dbo.Employee
FROM 'D:\EmployeeDetails.csv'
WITH (
        FIELDTERMINATOR = '|'
       ,ROWTERMINATOR   = ';'
       ,KEEPNULLS
       ,KEEPIDENTITY
     );

SELECT * FROM dbo.Employee;

4.

Importing Data with CHECK_CONSTRAINTS using BULK INSERT

Considering we have a check constraint at table level on the Salary column to allow only positive values which are greater than zero. If we do not specify CHECK_CONSTRAINTS option with BULK INSERT then negative values coming from the file will be ignored and inserted into the table. If we want to force and validate all the constraints while doing BULK INSERT to maintain data consistency then it is necessary to specify CHECK_CONSTRAINTS option.

CSV File Data

1|E100|Atul|100|IT;
2|E101|Chetan|200|HR;
3|E102|Niraj|300|Legal;
6|E103|Vishal|400|HR;
7|E104|Sangram|500|Leadership;
8|E105|Shailesh|-500|Legal;

Bulk Insert Query

DROP TABLE IF EXISTS dbo.Employee;

CREATE TABLE dbo.Employee
(
 SerialNo        INT              IDENTITY(1,1)
,EmployeeId      VARCHAR(100)
,Name            VARCHAR(255)     NULL
,Salary          INT              CHECK (Salary > 0)
,Dept            VARCHAR(100)     NULL
);

TRUNCATE TABLE dbo.Employee;

BULK INSERT dbo.Employee
FROM 'D:\EmployeeDetails.csv'
WITH (
        FIELDTERMINATOR = '|'
       ,ROWTERMINATOR   = ';'
       ,KEEPNULLS
       ,KEEPIDENTITY    
       ,CHECK_CONSTRAINTS
     );

Following error is generated while importing a negative value into the table with CHECK_CONSTRAINTS option enabled.

Msg 547, Level 16, State 0, Line 12
The INSERT statement conflicted with the CHECK constraint “CK__Employee__Salary__5812160E”. The conflict occurred in database “PracticeDB”, table “dbo.Employee”, column ‘Salary’.
The statement has been terminated.

5.

Importing Data with FIRST ROW using BULK INSERT

FIRST ROW property will skip the specified number minus ONE number of rows and insert all the rows following. In the subsequent text file, we have 6 total rows. Specifying FIRST ROW=3 will start importing at 3rd row (E102) – skipping the first 2 rows.

CSV File Data

1|E100|Atul|100|IT;
2|E101|Chetan|200|HR;
3|E102|Niraj|300|Legal;
6|E103|Vishal|400|HR;
7|E104|Sangram|500|Leadership;
8|E105|Shailesh|500|Legal;

Bulk Insert Query

DROP TABLE IF EXISTS dbo.Employee;

CREATE TABLE dbo.Employee
(
 SerialNo        INT           IDENTITY(1,1)
,EmployeeId      VARCHAR(100)
,Name            VARCHAR(255)  NULL
,Salary          INT           CHECK (Salary > 0)
,Dept            VARCHAR(100)  NULL
);

TRUNCATE TABLE dbo.Employee;

BULK INSERT dbo.Employee
FROM 'D:\EmployeeDetails.csv'
WITH (
        FIELDTERMINATOR = '|'
       ,ROWTERMINATOR   = ';'
       ,KEEPNULLS
       ,KEEPIDENTITY    
       ,CHECK_CONSTRAINTS
       ,FIRSTROW=3
     );

SELECT * FROM dbo.Employee;

Summary

In a nutshell, we have leanred the use of BULK INSERT process in SQL Server. Without using any other tool we can easily import data from CSV and other delimited file using SQL Server. Along with helpful properties we can import complex data following the options to maintain data consistency. If you like this post you may browse through Tech-Recipes SQL Server Database archive to enhance your knowledge.

Read more and browse through more posts related to SQL Server on Tech-Recipes.

1. Connect to SQL Server Database Using SQLCMD Utility
2. How To Deploy ISPAC File & SSIS Package From Command Line
3. SSIS- How To Export & Import ISPAC File SSISDB – Visual Studio
4. How To Create Database Diagram In SQL Server SSMS
5. How To Index Computed Column In SQL Server
6. How To Use Computed Column In SQL Server
7. Execute SQL Files Using SQLCMD