Unanswered: need some help, making many rows out of one, millions of times
so here's the deal:
i'm getting data in the form of an access db, which may be changed to a txt file due to size. each record has 2 columns at the end, the fields are EffFrom and EffTo, which are of type date and specify the date range for which the rest of the data in the record is valid. Here's the problem, i need to take those ranges and create a row for each day. i.e. if the range is 9/1/2003 to 9/15/2003 i would need 15 rows all with the same data except for a new date field which will replace efffrom and effto. seems like a cursor/loop issue to me, BUT there will eventually be millions of rows that need to be manipulated in this fashion. i started writing a stored procedure that will convert the data, do the necessary lookups [a few of the fields need to be resolved into numerical values before inserting them into the main table], but when i get to the point where i'm pulling the temp table into a cursor then going through row by row and making anywhere from 1 to 365 rows out of each row in the cursor, i'm shaking my head and feeling like there has to be a better way.
Ultimately, i'd like to do it through DTS, but i'm not very crafty with VBScript and opted to go the stored procedure/temp table route.
Here's what the data looks like
location_code1 varchar (will become int through lookup)
location_code2 varchar (will become int through lookup)
deptime varchar (string manipulation being done to add ':')
arrtime varchar (string manipulation being done to add ':')
carriercode varchar (will become int through lookup)
effto date -- described above
does anybody have some quick/dirty code or methods of creating multiple rows from one based on a date range [i know this goes against normalization, but the application requires the data to be this way and it cannot be rewritten]..or some DTS advice?
i'm stumped and in dire need of some inspiration. thanks in advance.
Once you create your sequential value table, use it in a query like this:
dateadd(dd, SequentialValue, EffFrom) as OnDate
from YourTable, SequentialValues
where SequentialValues.SequentialValue < DateDiff(dd, EffFrom, EffTo)
I didn't check this code for one-off errors or parameter order, but you should be able to get an idea of what you need to do.
Note: this method works well, but if you are going to use it against a table with millions of rows, don't include sequential values in your table greater than the largest datespan you expect, in order to keep the runtime down.
awesome....the data is definitely lookin good as far as generating the multiple rows...now to incorporate this into a huge data load.
would you suggest a stored procedure with a variable of type table then a mass insert? or something through DTS? i'll prob try a few different methods and evaluate the speed, but any advice would be appreciated.