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.

more ▼

asked Oct 03, 2010 at 02:07 AM in Default

avatar image

1 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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

more ▼

answered Oct 04, 2010 at 05:52 AM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

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, 2010 at 05:57 AM WilliamD
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 03, 2010 at 06:47 AM

avatar image

40.9k 39 94 168

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 03, 2010 at 03:12 AM

avatar image

Håkan Winther
16.6k 37 46 58

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 03, 2010 at 02:07 AM

Seen: 2230 times

Last Updated: Oct 04, 2010 at 06:04 AM

Copyright 2018 Redgate Software. Privacy Policy