x

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 '10 at 02:07 AM in Default

adnan gravatar image

adnan
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][2]

[2]: http://www.sqlcat.com
more ▼

answered Oct 04 '10 at 05:52 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.7k 43 49 76

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)
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 '10 at 06:47 AM

Tim gravatar image

Tim
35.5k 32 40 138

(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 '10 at 03:12 AM

Håkan Winther gravatar image

Håkan Winther
15.5k 33 37 48

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x898
x62
x38
x30

asked: Oct 03 '10 at 02:07 AM

Seen: 1766 times

Last Updated: Oct 04 '10 at 06:04 AM