best way to import text file into relational database?

[Glowworm27]

Hello everyone,

I have some text files that I want to import into a database, and I have designed the database to be relational.

Currently I am reading the text file one line at a time, and running stored proc to import the data.

In the stored proc I run another proc to see if the primary table and the relation ID I need to insert with the data, if it does I return the ID, if Not I create the ID, and then return the newly created ID,

I then return control back to the orignal proc, and with the ID of the primary table I check to see if the data exits in the secondary table using the ID from the primary and another key field in the secondary, if the record exists then I do an Update, otherwise I insert a new row with the appropriate Key from the primary table.

Easy enough???

Well the text files can contain over 10,000 lines to be imported, which means running these procs over and over and over every few milliseconds, until I reach the end of the text file.

in the case of a normal file 1.5 megs it can take upwards of 30 minutes to inport the file.

I know I want to use DTS it is just so darn fast, problem is how do I get the relational data to be inserted with the data? i.e. the primary key into the secondary table where the data would be inserted, and how do I update data that already exists? using DTS?

Hope this makes sence.
Thanks in advance



George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[whatduh00]

How about just importing the raw data into a table in your database using dts first. Once the data is in this "temporary" table you can modify the procedure you have right now to look at the new table.

This does add a step to the process but it may cut down on the amount of time overall since dts or bcp will import the data quickly.

 

[gcole]

whatduh00 is right. You can run all your code in a dts job. You can either schedule the job or run it from an sp.

 

[nigelrivett]

Or you can run it all in an SP.
Why use a client app for something that is essentially a database job.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.