Import CSV File Into SQL Server Using Bulk Insert

25. May 2009 15:51

T-SQL  6 Comments

You have large data to import to SQL Server, and you have all the data in a CSV File (Comma Seperated Value file). Are you going to map the field one by one? 

OR just use a small function od SQL to import everything in the table. But before you proceed with this you need to map each column of CSV with the one with your table column. Look at the below quick example on how to use the BULK INSERT to import CSV file data into SQL Server.

Step 1: Create a CSV file.

Step 2: Create a table and map the fields accordingly: (Check below screenshot)


Here is the CODE:

FROM 'c:\TestData.csv'  -- Full path of the CSV file
FIELDTERMINATOR = ',', --CSV field delimiter
ROWTERMINATOR = '\n'   --Use to shift the control to next row


Currently rated 3.7 by 3 people

Comments (6)

Brian Brian
2/1/2010 10:21:22 PM #

Your post has been the most helpful of all. Is there a way to insert a csv file that is on a different computer? Thanks for your help Brian

Prashant Prashant
2/2/2010 8:48:26 PM #

Yes you can Brian...... In that case your path for the CSV should be the one where you CSV file is placed. In my case it is at '\\ENN-14\Shared Folder\Test csv'. Right now I am using a machine on a domain so cannot show how to get the file from the C or D drives. But in that case you can have the path of the CSV as '\\ENN-14\D$\Shared Folder\Test csv' Note the D$ represents the drive letter BULK INSERT TestTable FROM '\\enn-14\Shared Folder\Test.csv'-- Full path of the CSV file WITH ( FIELDTERMINATOR = ',', --CSV field delimiter ROWTERMINATOR = '\n' --Use to shift the control to next row )

Brian Brian
2/3/2010 9:02:06 PM #

Prashant, Thank you so much for your help. Brian

kani kani
1/27/2012 2:43:37 PM #

Thanks u. Is der any possible to add static column value in this bulk insert. Current date column.

Prashant Prashant
1/27/2012 10:09:22 PM #

I haven't tried that, but you can set the default value of the column in the create table script above and then try using the bulk insert. I hope this would work.

Anupam Tiwari Anupam Tiwari
10/20/2012 1:56:00 AM #

Hi i understood those thing but what to do if csv file have no "rhoterminator". I have a cvs file which is not have any rhoterminator and i have to open that file and save it to in the tab delimited format and then this query will work.

Add Comment

Visit to discover Indian blogs Computers Blogs