Best way to dump 250,000 records to file? 1

[DugsDMan]

Does anyone know the best way to dump large amounts of data from SQL Server to a text file, automatically?

Here's the scenario:
I have a web application written in ColdFusion, with a SQL Server backend. The application reads 7 mainframe files each morning. This information contains data on jobs that ran the night before (job name, start time, return code, etc.). This application is used to calculate trends, view reasons for abends, view program statistics, etc. There are roughly 800 records per day added to the database.

Right now, I have data back to 2002. There are 236,216 records for 2002. I'd like to dump that data to a text file (something like Historical Data 2002.txt). I'd also like to be able to schedule something to run on January 1 each year to get rid of anything over a year old. That way, there will always be the current year and the previous years data in the reports.

I've created a simple CF page to export the data, but it takes forever. It exported +/- 36,000 records in 10 minutes. This won't work in my situation, because our processes are limited to 2 minutes on our prodcution server. I've also created a DTS package and it works fine (runs in about 1 minute). However, I need to be able to dynamically name the output file so that it doesn't overwrite the old ones when it runs each year.

Does anyone have any ideas?

Thanks,
Doug

 

[NoCoolHandle]

You need to use the BCP utiltity. It is designed for what you are trying to do. Do a search on BCP in books on line and you should find the clues you need.

HTH


Rob

 

[DugsDMan]

Thanks! I think that and a stored proc to remove the data once I export it will do the trick. I'll have to look it over more and try it out.