Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Location
    DC Metro
    Posts
    27

    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)
    efffrom date
    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.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    See this thread for help on using a table of sequential values to "fill in" dates in a date range:

    http://dbforums.com/showthread.php?threadid=914261


    Once you create your sequential value table, use it in a query like this:

    Select YourFields,
    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.

    blindman
    Last edited by blindman; 09-19-03 at 00:35.

  3. #3
    Join Date
    Jul 2003
    Location
    DC Metro
    Posts
    27
    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.

    thanks!!!!

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I've never liked DTS, and use it mostly for simple data transfers.

    A temporary table would probably process fastest, but a permanent table would only need to be created once. Either way probably won't make a large difference.

    blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •