How To Import CSV File Into SQL Server

BULK INSERT SQL query can be used to bulk insert data into a database table. The query imports data from a data file into a database table or view in the specified format. We can create and execute SQL query direct on SQL Server database.

Here is the BULK INSERT syntax:

BULK INSERT
{ database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }
FROM 'data_file'
[ WITH
(
[ [ , ] BATCHSIZE = batch_size ]
[ [ , ] CHECK_CONSTRAINTS ]
[ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
[ [ , ] DATAFILETYPE =
{ 'char' | 'native'| 'widechar' | 'widenative' } ]
[ [ , ] DATASOURCE = 'data_source_name' ]
[ [ , ] ERRORFILE = 'file_name' ]
[ [ , ] ERRORFILE_DATA_SOURCE = 'data_source_name' ]
[ [ , ] FIRSTROW = first_row ]
[ [ , ] FIRE_TRIGGERS ]
[ [ , ] FORMATFILE_DATASOURCE = 'data_source_name' ]
[ [ , ] 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 ]
-- input file format options
[ [ , ] FORMAT = 'CSV' ]
[ [ , ] FIELDQUOTE = 'quote_characters']
[ [ , ] FORMATFILE = 'format_file_path' ]
[ [ , ] FIELDTERMINATOR = 'field_terminator' ]
[ [ , ] ROWTERMINATOR = 'row_terminator' ]
)]

Here is SQL query example that imports data from a csv file into the Customers table.

BULK INSERT Customers
FROM 'C:\Data\Customers.csv';

For testing purposes, let's create a table and add data into it. If you already have a table with data, you can skip these steps.

Step 1. Create a database table. Open your SQL Server Management Studio and create a new database table.

CREATE TABLE Test
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40))

If you're new to CREATE TABLE, I highly recommend reading this article, Create, Insert, Update Database Table In SQL.

Step 2. Create a CSV or Text file and add this data to it in exact same oder. Save the file as test.csv in C:\ folder or whererever you like. The file has 4 rows with comma seperated values.

1,Pankaj,pandey
2,Rahul,Pandey
3,Ramesh,Mishra
4,Raja,Singh

Step 3. Write BULK INSERT SQL query and provide the table name and the full path of the .csv file.

BULK
INSERT Test
FROM 'D:\test.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

Execute this query on your SQL Server database.

Step 4. Check the results.

Execute the following SQL query.

SELECT * FROM Test

You should see same data as it was in the csv file.

Want to learn how to implement bulk data import in a C# application? Here is a detailed article, Import Excel Data Into SQL Table Using C#

2 Comments

Join the conversation! Your thoughts help the community grow.

Rasadul Alam Rashed13y ago
Some times we need to insert massive data from CSV file to database. There are several ways to insert bulk data from a CSV file to a database. During this insertion time we need to consider the processing speed and time. Some common well known techniques are as follows. SQL BULK INSERT Query BCP or SqlBulkCopy library to insert bulk data using C# or VB SQL Server Integration Service (SSIS) Normal SQL command library in C# or VB A good example with source code :http://cybarlab.blogspot.com/2013/03/bulk-data-insertion-from-csv-to-sql.html Hope it will help you.
Kiran Maram13y ago
nice