Load data from file

[rfazendeiro]

Hi to all,

 

i'm currently making an application that has to read data from a file (csv) and insert / update that information to a database (sql server)

 

The file that i need to read has at least 20000 and the data that existes in the database is equally large so i guess reading all that information to memory is not a good idea.

 

I have a thought but i'm a little lost in this so some help would be apreciated.

 

My ideia is to execute an dts that would copy all the information to a table and then i would execute some store procedures that would insert / update the information.

 

is this the best method? and if i do it this way how could I retrieve errors so that i could produce a log of some sort.

 

thx

[PlausiblyDamp]

DTS is a pretty good tool for doing this kind of thing and would definately allow you to do the import / run stored proc kind of thing.

If you have SQL 2005 it contains a replacement for DTS Sql Server Integration Services (SSIS) which is a big improvement over DTS and gives much better handling of errors etc.

[rfazendeiro]

I'm using sql server 2000 and have to stick with this.

 

I have already been successful at executing a DTS with parameters throught c#. Now i just need to catch those errors so i can make my log.

 

I have one question about the dts. What it does is read a csv file and put it to a temporary table. I then execute some store procedures that actualy do the database update.

 

So if there is any kind of error during the dts executing (transfer the data from file to temprary table), for example in a row, does the whole procedure just rollback or does it continue? and if its stops because of one row, is it possible to signal it to continue with transfering the rest of the data to the temporary table?

[emp]

Same question

 

I'd also appreciate any pointers to this, as I am working on a project trying to populate a DB with values from a file and calculated values.

 

Thanks,

 

:: emp ::