question

adnan avatar image
adnan 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.
ssisimport-databcpbulk-insert
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image
Håkan Winther answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image
Tim answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Agreed on that. Thomas' presentations are quite eye-opening. I would be suprised if the tuning recommendations from Thomas were pertinant to most loads though - we don't all have to load TBs of data in record time!
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.