|
|
|
Forum Newbie
Group: Forum Members
Last Login: 7/29/2008 5:44:00 PM
Posts: 3,
Visits: 11
|
|
Hi guys,
I have a 43000 line query out of necessity as the data I’m querying requires a very large amount of spool space to produce results so I’ve had to break it down and specify smaller ranges as I have limited spool space to work with.
For each data set I get returned I have 32 previous statements before the 33rd select statement which returns the data. All the other statements create volatile tables and put data into those volatile tables.
I have to repeat this 26 times to get enough data for a year as I can only get 2 weeks worth of data at a time or else I run out of spool space. So I’ve just strung this all together into a 43000 line query and every 33rd statement I get a result (excluding the drop table statements).
Last time I ran this, I ran it for 6 months worth of data. It returned 184 million lines and took 02:26:13 to run.
I’m now running it for a years worth of data and just have it ticking away in the background and as data is returned for every 33rd statement I just copy and paste into excel.
I would like TD to automatically return the data to an access database file on the drive each time data is returned while it ticks away in the background rather then having to manually copy and paste the data and/or manually use the export results function making the process very manual.
I also can’t just keep adding to the volatile tables as they’ll get too big and I'll run out of spool, so each time I get data back I have to delete all of the volatile tables and start again from scratch for the next 2 weeks worth of data.
Everyone I've spoken to says I have to use the export results function manually.
Surely there has to be code that predefines an export telling it to add what to where before running the data returning statements!
Can anyone help?
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Today @ 7:30:07 AM
Posts: 197,
Visits: 611
|
|
Hello,
The following can be done to achieve this:
- You need to know the total number of columns you need to export
- You can write a UDF, which (if output file doesnot exists, creates it, or appends it) writes parameters passed to it in a file.
- This will cause I/O hit, and you must be good in C/C++ to write an error-free code, otherwise it may cause VProcs to fail!
Example:
SELECT udfWriteToFile("File1.csv", Col1, Col2, Col3) FROM Table1;
Output:
File1.csv containing values as:
col1value,col2value,col3value
col1value,col2value,col3value
col1value,col2value,col3value
...
Regards,
Adeel
|
|
|
|