Load Comma Delimited file (csv) in SQL Server

We often need to import data that comes from different data sources, into a database like SQL Server. Usually data comes in the form of a comma delimited file aka CSV file.

Note: To handle complex scenarios and large files, use a utility like DTS/SSIS (SQL Server Integration Services). However for simpler scenarios, the approach shown in this article works fine.

These are the two easy ways to import data from a CSV file into a table of a SQL Server Database – Using Bulk Insert and Using SQL Server Management Studio.

Consider the following data

1,test,89300
2,testing,52801
3,test,1000
Create a file name test.csv in your system and add the data shown above in that file
Create a test table
CREATE TABLE test(
id int,
names varchar(100),
amount decimal(12,2)
)
Method 1: Using Bulk Insert

Here’s the same query for you to try out
bulk insert test from 'F:\test.csv'
with
(
fieldterminator=',',
rowterminator='\n'
)

The above code reads data from the file located at F:\text.csv and splits data into different
columns based on the fieldterminator ‘,’ (comma) and into different rows based on the rowterminator '\n' (\n is for newline).

Now if you do a SELECT * FROM test you will get the following output