|
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.
(comments are locked)
|
|
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 (when they become available - should only be a few days off now), and maybe even checking out his and his colleagues' work on SQLCAT 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!
Oct 04 '10 at 05:57 AM
WilliamD
(comments are locked)
|
|
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.
(comments are locked)
|
|
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.
(comments are locked)
|
