asked
Import text file in sql 2005
we have few text files, which have huge no of records and I have to import into staging area (temporay tables) for further processing. I just wanted to know which is best way to import them into tables. 1. BCP 2. Bulk Insert 3. SSIS 4. Or any other best way.
With a huge number of records I would use SSIS with fast load option and set the batch size to a couple of thousands. BCP and bulk insert can also be uesd, but if you later decide that you need some kind of workflow, transformations or parallell processing then the SSIS is the best option without having to rewrite anything.
I agree with @Hakan, SSIS would be the best long term decision. Where are these flat files coming from, is someone else in your organization producing them? Is it a data dump from another database, could you fetch this data yourself? I find this many times at my shop where I am asked to import data to do some transform of it only to extract it to another format. At least half the time I am able to pull the data myself from their SQL or MYSQL database and not have to touch their "report". Best of luck.
I sat in on Thomas Kejser's session on "Designing and Tuning high speed data loading" at SQLBits 7, where he showed that SSIS was fast, but in some circumstances could be beaten by BCP. I would recommend getting the video and slide deck from the [SQLBits website][1] (when they become available - should only be a few days off now), and maybe even checking out his and his colleagues' work on [SQLCAT][2] [1]:
http://sqlbits.com/Agenda/event7/Designing_and_Tuning_high_speed_data_loading/default.aspx [2]:
http://www.sqlcat.com
