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.