DTS 1 Textfile with 2 types of records to 2 tables... doable?

[casorter]

Hi everyone,

I have an import function that i am currently doing programatically, parsing through a text file massaging the data and putting it into 2 tables .. lets call them summary and detail...
here is a look at a simplified view of the text file
<recordID>,<SummaryID>,<SummaryData>
<recordID>,<summaryID>,<DetailID>,<DetailData>
<recordID>,<summaryID>,<DetailID>,<DetailData>
<recordID>,<summaryID>,<DetailID>,<DetailData>
<recordID>,<summaryID>,<DetailID>,<DetailData>
<recordID>,<SummaryID>,<SummaryData>
<recordID>,<summaryID>,<DetailID>,<DetailData>
<recordID>,<summaryID>,<DetailID>,<DetailData>
<recordID>,<summaryID>,<DetailID>,<DetailData>
.
.
.
the <recordID> field at the begining of each line identifies it to the parsing program as a summary or detail record.

ok.. the problem is that reading through all these records (up to 5 meg) takes a long time. I know that DTS is faster but i don't think that i can do this like i want to with dts .. because dts maps the entire input file to a specific schema at the front end.. and my textfile contains different types of records... Is there a way to do this faster?

thanks for the help.
Mark

 

[nigelrivett]

I would import into a staging table and use an sp to manipulate. You can do yhis in a single query for each record type.

You can also do the import via bcp and put everything in SPs.



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