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

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

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

We will create dummy flat file or csv  under below location for BULK INSERT example

‘I:\DBA\flatfilename.csv’

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.

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.

Leave a Reply

  

  

  


+ 8 = 15