Importing CSV in SQL Server: Sql Bulk Insert

Posted: December 29, 2011 in Notes, SQL Server
Tags: , , , , , , , ,

When you are dealing with bulk data then all you have is a CSV file. Comma Separated Value File or CSV file is a data file which contains large amount of data separated by comma.

The scenario is you have to insert the given data in SQL Server and the question is What will you do?

Option 1: Map fields one by one

Option 2: Use a small function called Sql Bulk Insert to insert data in the table.Option 1 is a difficult concept and in this post we will discuss about the Option 2.

Prerequisites Of Sql Bulk Insert

For sql bulk insert all you need is:-

1) A CSV file with data

2) A table to contain data

3) SQL Server (this requirement is most important)

Lets start it with a simple example.

We have a table defined below:

create table test( sno int, name varchar)

And we have a CSV file like

1,abc

2,cde

Syntax for Bulk Insert is:

bulk insert “tablename” from “pathoffile”

with ( parameters)

Using the above syntax we can write a command to insert the data and it will be:-

bulk insert test from ‘c:/abc.csv’

( fieldterminator=’,’,

rowterminator=’/n’) 

/n denotes the new line and sometimes you can get a /t which means tab. So if you got a tab separated file then you can use the bulk insert like given below.

bulk insert test from ‘c:/abc.csv’

( fieldterminator=’/t’,

rowterminator=’/n’) 

Sometime you can get a file which have header rows with it then your query will change a little bit. Lets understand this with the help of an example.

Consider a CSV file like given below:-

sno,name
1,a
2,b

In the above CSV file we have headers given and if we try to import this using the query we have discussed above we will receive an error. So in this case our query will be similar to below query.

bulk insert test from ‘c:/abc.csv’

(firstrow=2

fieldterminator=’,’,

rowterminator=’/n’)

Thats All for now, Hope you like this post.

I will try to update more like this till then keep playing with codes.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s