Insert data from CSV file into SQL Server table

Introduction:

Here, I will explain how to insert data from CSV file into SQL Server table.

Description:

Recently, I did one task with requirement to insert bulk data from CSV file into SQL Server table. As you know, this is very simple task if we have same number of columns with same sequence in CSV file as table.

But to accomplish this task, I had CSV file containing same number of columns with same sequence as table EXCEPT ID column of table which was Identity Column with auto increment value. I had tried with various scripts but 1 column was missing in CSV file from table so NO scripts were allowing me to insert data into table.

To resolve error, I simply added dummy ID column into CSV file and data inserted properly into table.

Student table in SQL Server :

Original CSV file (Student.CSV) :

Updated CSV file :

You can use following script to process CSV file and insert data into table :

BULK

INSERT Student -- table name

FROM 'E:\Student.csv' -- Path of CSV file

WITH

(

          FIRSTROW = 2, -- This is used to start reading process from Row 2 of CSV file.

          FIELDTERMINATOR = ',',

          ROWTERMINATOR = '\n'

)

Data in Student table after running script :