Inserting dummy rows into a table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Inserting dummy rows into a table

  1. #1
    Join Date
    Feb 2000
    Location
    Mumbai
    Posts
    2
    suppose i've got a table named para_tab with two columns namely from_dt date ,to_dt date.
    The table contains only one row with values such as
    from dt='01-mar-2000' and to_dt ='31-mar-2000' .
    I want to insert dummy rows starting from the from_dt and ending with the to_dt into the same table with an sql statement using the same table.
    for eg:
    the difference of the above mentioned dates is 31 days.
    so i want to insert 31 rows into the same table with an sql
    statement without using any other table.
    This should be done only with and sql statement,
    and only one table which contains only one row.




  2. #2
    Join Date
    Jul 2000
    Posts
    296
    Use a loop in PL/SQL.

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    The problem is that he listed 2 requirements:

    - Only SQL (ie no PL/SQL)
    - No other tables

    Personally, I always create a DateDimension_T table that holds a record for every day across several years. It is then very easy to turn a date range into the proper number of rows. However, my use of a secondary table is also precluded.

    I'd be interesed in what you get back for this one. Short of hard-coding the proper number of unions, I don't think it can be done. The UNION solution would be something like joining your table against
    SELECT 1 FROM DUAL UNION SELECT 2 FROM DUAL...
    using the end_date minus the from_date to join, then adding the from_date back to the result to generate the target date. I'd explain it better, but it's a terrible solution anyway, as you are limited by the number of UNIONs you do. It becomes very hard to handle a multi-year range :)

    (Watching to see if somebody finds something)

    - Chris

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    I'm not sure if I understand Sudhir's requirements correctly - this is what I *think* he wants:

    - He has a the starting and ending date in a table and he wants to insert one record for each day between this two dates. He said he want's to use only SQL and no other table. Since Chris have already mention the use of DUAL (which *is* "another table") I suppose the usage of public views like ALL_OBJECTS is allowed. So how about this:

    INSERT INTO para_tab(from_dt)
    SELECT pt.from_dt+x.row_num-1
    FROM para_tab pt,
    (SELECT rownum row_num FROM all_objects
    WHERE ROWNUM <= (SELECT to_dt - from_dt +1 FROM para_tab)) x;

    This will insert a single row for every date between the reference dates (from_dt and to_dt). The join
    obviously results in cartesian merge join, but as there is only one row in PARA_TAB initialy this bares no performance penalty. BTW, in 8i any user has (at least) about 10.000 rows available in ALL_OBJECTS, so this query can span a date range of at least about 30 years.

    HTH,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Too, too sweet!!!

    I can't believe I missed that. That is sooo cool!!!

    You replaced my hokey UNIONed list with a readily-available one - very nice!

    Of course, just to do my usual Devil's advocate bit, there *are* some minor issues with such an approach.

    - The Oracle 'views' are generally terrible performance-wise. Using them in regular SQL is a chancy thing at best. Again, I love this solution - just trying to show the problems as well.

    - You can't do an EXPLAIN PLAN on a query that uses an Oracle view unless you have major rights, which most people do not. This makes it even riskier to include these views in complicated or time-intensive SQL.

    - The meaning of what you are doing is a little hidden. Having an actual DateDimension table makes the functionality obvious, and optimizable, with everything being completely under your control.

    However, given the incredible restrictions of this particular post, jmodic turned in an outstanding answer!

    </me tips hat>

    - Chris

Posting Permissions

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


Click Here to Expand Forum to Full Width