How to insert .CSV in SQL Server
Here i will explain the basics steps and codes need to follow while inserting an .CSV file into a SQL database. This article will help when you have a large data in your excel files and you need to do some comparison or operation with the data available in SQL database.
In my earlier article, i explained how to insert an excel file to a PLSQL database. FOr reference, here is the URL of that article-
http://www.dotnetspider.com/resources/45710-How-to-insert-Excel-files-into-PLSQL-database.aspx
In this article i am going to explain how we can insert a .CSV file in SQL server database. Inserting a csv file in database is very easy. This will help, when you will deal with the large data.
To explain, consider an example-
we need to insert list of states in India. If we will insert one by one it will take lot of time. So better save the data in a csv sheet (Create an excel sheet and save it as a .csv file). Your csv file will have 2 column-
Id and State Name.
Save it at any accessible location of your computer. Now create a table in your database, like the one i have created StateList as below-
create table StateList
(
StateId int primary Key Identity(1,1),
StateName Varchar(100)
)
Run it. Now you have one table with two column, Id and name. Remember the column in your csv sheet and in your table should be same.
Please don't include header in the csv file
Now insert your already prepared sheet into the database. For this you can use the below code-
bulk
insert StateList
from 'C:\State.csv'
with
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
go
Here, "bulk" is the keyword used to insert bulk data.
insert StateList- StateList is the name of the table, you have created.
from 'C:\State.csv' - Is the location and name of the file. I have kept my file in C drive of my PC and file name is State.csv. FIELDTERMINATOR and ROWTERMINATOR is used for the separation purpose of records.
Just select this code and run it. All the records of State.csv will be copied into the table. To check the data just use-
select * from StateList
By using the above simple steps, you will upload large data in almost no time.