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.