Monday, November 10, 2008

BULK INSERT in MS SQL SERVER

In IT environment, it is always a necessary to import data from data files into database.

And recently, I have to deal with import a huge load of data (around 200 MB for 1 CSV input file) from CSV files into MS SQL SERVER. So what I had done to achieve this is by using BULK INSERT.

Firstly, prepare a CSV data file with the following content and save as test.csv.
July,Singapore,25
James,Australia,50
May,China,29


And then run scripts as following to load all the data from the CSV file into database.

--Temp table to store the data
CREATE TABLE CSVTest
(
   Name VARCHAR(500),
   Country VARCHAR(500),
   Age INT
)
GO

--Bulk insert into CSVTest
BULK INSERT CSVTest
FROM 'C:\test.csv'
WITH
(
   FIELDTERMINATOR=',',
   ROWTERMINATOR = '\n'
)
GO

--Get all data from CSVTest
SELECT *
FROM CSVTest
GO

--Drop the temp table
DROP TABLE CSVTest
GO

3 comments:

Anonymous said...

I am getting a following error.
"Operating system error code 53(The network path was not found )"

ejuly on December 4, 2008 at 11:13 AM said...

Hi, are you trying to import a file that is located on a remote computer?

If so, please make sure that the file is placed in a network share that can be accessed through the computer that running SQL Server.

Anonymous said...

Good brief and this mail helped me alot in my college assignement. Gratefulness you as your information.

 

Get paid for your opinions! Click on the banner above to join Planet Pulse. Its totally free to sign up, and you can earn UNLIMITED. Find out more by visiting PLANET PULSE.
July Code Blog Copyright © 2010 Blogger Template Designed by Bie Blogger Template