10GB CSV import into SQL 2008

Question

10GB CSV import into SQL 2008

98cwitr

SSC Enthusiast

Points: 134
More actions
November 3, 2011 at 12:32 pm

#262317

Hey all, we have a 10GB csv file (yeah...I know...) that we are going to need to import into SQL 2008. Before we continue the metadata collection, we are wondering if this is a futile effort. Will we be able to import the data? We understand that it could take a day or 2, but are willing to let the import run over a weekend or several days...just wondering if it's not possible to do so with such a large file.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

If using BCP or SSIS, be sure to set an apprpriate batch size (number of rows per batch). You don't want the import to run as a single batch.

sturner SSC-Insane Points: 22411 More actions · Answer 2

I'm not aware of any file size limitation for importing data into SQL server.

Things you can do to make the import operation as efficient as possible:

1) put the database into simple mode (if possible) or bulk logged, or use a stage database

2) Make the database file size to be 10GB or larger before your do the import.

3) use BCP on some sample data to make sure of the format specs.

4) ditto on smaller batches

The probability of survival is inversely proportional to the angle of arrival.

Lowell SSC Guru Points: 323546 More actions · Answer 3

sturner (11/3/2011)
I'm not aware of any file size limitation for importing data into SQL server.
Things you can do to make the import operation as efficient as possible:
1) put the database into simple mode (if possible) or bulk logged, or use a stage database
2) Make the database file size to be 10GB or larger before your do the import.
3) use BCP on some sample data to make sure of the format specs.
4) ditto on smaller batches

i was going to mention the same thing.

FWIW, i've imported a 700 meg text file in around 10 minutes, so i don't think you'll have to worry about an over-the-weekend issue; you'd be able to test-bulk insert that file plenty of times in a single day.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

98cwitr SSC Enthusiast Points: 134 More actions · Answer 4

Awesome! We had a few concerned colleges, but thank you all for the reassurance! 🙂