Re: Creating an insert script from a select in sqlplus

one option could be:

    insert into table_name

                (CLASSNBR           ,
                 STARTDATETIME,
                 ENDDATETIME    ,
                 LOCATION             )
    select  CLASSNBR           ,
                STARTDATETIME  + &start_days,
                ENDDATETIME      + &end_days,
                LOCATION

or probably we can make use of a PL/SQL script.

/* This script will create 5 copies for each record in the table.

    Every new record will have a date equal to the previous record

    plus one day                                                        */

declare

    cursor c1 is

    select  CLASSNBR           ,
                STARTDATETIME ,
                ENDDATETIME      ,
                LOCATION
       from table_name;

    num_copies number := 5; /* create 5 copies for the next 5 days */

begin

   for cur_aux in c1 loop

      for day_num in 1.. num_copies loop
         insert into table_name
                         (CLASSNBR           ,
                          STARTDATETIME,
                          ENDDATETIME    ,
                          LOCATION             )
             values  cur_aux.CLASSNBR           ,
                          cur_aux.STARTDATETIME + day_num,
                          cur_aux.ENDDATETIME     + day_num,
                          cur_aux.LOCATION             ;
      end loop;

bkm4841_at_my-dejanews.com wrote in message <77iflk$seb$1_at_nnrp1.dejanews.com>...
>I've done this before, but not recently and can't find it in my oracle
>reference. I'm trying to create a schedule in an oracle table....
>
> Name Null? Type
> ------------------------------- -------- ----
> CLASSNBR NUMBER
> STARTDATETIME NOT NULL DATE
> ENDDATETIME NOT NULL DATE
> LOCATION NUMBER
>
>I have the classes in there for one day. I want to duplicate that data for
>many more days, so I want to create an insert script from a select of this
>table and increment the startdatetime and enddatetime by one day for
>each iteration of the entire list.
>
>So, my two problems are...
>
>1) Syntax for creating the insert script and
>2) Incrementing the days by 1
>
>Any help would be appreciated.
>
> -bkm
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Thu Jan 14 1999 - 02:17:17 CET