SQL Server – How to load flat file into table – Using BULK INSERT

As SQL Server DBA, you gets request to load flat file data in sql server table, here is the small example of loading flat file or comma separated file data into table using BULK INSERT method.

How to load flat file into table – Using BULK INSERT

To understand this method, first we will create dummy flat or csv file under any location that you want to create, in my case I am using ‘I:\DBA\flatfilename.csv’ location for this demo.

BULK INSERT example

Now you connect to SQL Server Management Studio, Click on new query

Create temporary table to load data into this temp table

DROP TABLE #tmp
CREATE TABLE #tmp (
userid VARCHAR(8000),
email_id VARCHAR(8000) );

Once table is created, use below BULK INSERT command to load data into temp table created in above step

Example

BULK INSERT #tmp
FROM 'I:\DBA\flatfilename.csv'
with (FirstRow = 1, FieldTerminator = ',', RowTerminator = '\n');

To understand more clear, below are the explanation for each parameter.

BULK INSERT command with File location

BULK INSERT #tmp
FROM 'I:\DBA\flatfilename.csv'

Give your file location, this file should be on the same server and SQL server should have read access to this file.

FirstRow

This parameter is use to consider first row as column name in flat file.

FieldTerminator

We are using comma separated file (csv), here data will be separated using comma.

RowTerminator

As name suggests, it is used to distinguish the next row in the file.

You can verify the data load using below select statement

SELECT * FROM #tmp

This way you can load data into any table of SQL server, Hope you enjoy reading this. If you experience any problem while loading data using this method, Please share your comment below.

SqlServer-query