Updating records - better way?

[DebbieDavis]

Hello,

I have a large text file with part numbers and descriptions that is parsed out and dumped into a table called estimates. Then I need to go back and run a query to update all of those records with a price from a separate table called parts. There are usually 100 records in each estimate. When I create a view linking the new table with the parts table and update by do looping, it takes so long that it sometimes times out. I need a better way to do this. I'd appreciate any thoughts. Thanks.

 

[onpnt]

-so the data collection is done to a text file

-you come along, parse it and send the data to the table

-then you run a UPDATE statement each iteration of a loop to add a price <--that's it?

was that 100 fields per estimate, or 100 estimates to add the price field to?

This can be done in stored procedures without much advanced knowledge in SQL really. ASP isn't really made for batch processing, but can be of course. The reactions to that are of course what you are running into.

I jsut wanted to make certain of this part first before thinking something up
-then you run a UPDATE statement each iteration of a loop to add a price <--that's it?


___________________________________________________________________

http://www.onpnt.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page

 

[Genimuse]

Aye, that's definitely something you should do (and should be able to do) entirely in the database via SQL. The stored procedure wouldn't be too tough to write, and I'd bet someone in the SQL forum could help you whip it together quickly.

 

[onpnt]

A thought came to me...being the DBA at heart I am

You said

Then I need to go back and run a query to update all of those records with a price from a separate table called parts.

Sounds like you're creating a redundant situation. Not very normalized to say it another way.

If you have a table parts holding per say
part_id
part_desc
part_price

And you then come along with an estimates table holding something like
estimate_id
estimate_date
estimate_part_id
estimate_desc

From what you said all I see the need for is that for the needs at hand. The only updating I can figure is the estimate_part_id, which will relate to a SELECT match on part_id. (there's your relation)

Gains? Speed, normalization and data stability

One other gain that I see is you take out the need for a more tactful SQL statement to update your original price field. Now all you have to do is a sub query to grab the part_id and insert it (if match found) into the estimate_part_id. At the time this process has completed, you have a very easy to query relationship. A simple JOIN will give you
estimate_date
part_desc
part_price
estimate_desc
…more then likely quantity and totals will be there also…

I guess that’s by 2 bits. ? Just wanted to throw it out there sense it came to mind

Good Luck!!


___________________________________________________________________

http://www.onpnt.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page

 

[DebbieDavis]

Thanks to both. It all begins with a large text file that is generated by an old foxpro program. Originally, the text file was imported into an old unix system. Now it's going into a SQL 2000 database table. The part numbers and descriptions are in the original text file. The prices, and any other pertinent info are in the parts table. So, the only thing I know how to do is import the text file, then run a query linking the newly uploaded data to loop through the records and update them with the prices. I'm sure there is a better way to do this, but until that old foxpro program is rewritten, I'm stuck with it. I don't know how to use stored procedures or how to incorporate one into my application. Thanks again!!

 

[PatrickIRL]

Why not set up a DTS in SQL server and have it run during the night?

Regards,

Patrick