Forum Stats

  • 3,759,060 Users
  • 2,251,495 Discussions
  • 7,870,477 Comments

Discussions

How to insert additional records based of another

User_JP93M
User_JP93M Member Posts: 1 Green Ribbon

Hi everyone,

I have a record like this:

some_id | … | starting_date | frequency

34 | … | 2021/01/04 | 14

35 | … | 2021/01/18 | 14

Where "starting_date" is a date and "freuqncy" is a number that explains after how many days another record (here 35) is to be inserted.

Is there a way to do this automatically in Oracle SQL?

Thanks in advance

Markus

Tagged:

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,060 Red Diamond

    Hi, @User_JP93M

    Is there a way to do this automatically in Oracle SQL?

    Not automatically; you'll have to write an INSERT statement, like

    INSERT INTO table_x (some_id, starting_date, frequency)
    SELECT some_id + 1
    ,	starting_date + frequency
    ,	frequencey
    FROM	table_x
    -- WHERE ...
    ;
    

    Without a WHERE clause, this makes a new row for each existing row in the table. The some_id of the new row will be 1 greater than the some_id of the original row.

    I hope this solves your problem. If not, post CREATE TABLE and INSERT statements for a little sample data, and the results you want from that data (that is, what the table should look like after the new rows are inserted).

    See the forum FAQ: How to Ask Questions in Developer Community Spaces (oracle.com)

  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,587 Silver Crown

    Oracle version? There could be solutions that are available or not based on your version, never forget to include that information.

    You can do a recursive query, but will have to somehow tell when to stop (you do not want to keep adding rows forever....)


    Please explain more in detail your problem, include the table definition. I guess you have a row with ID = 0 that is your "starting" date.

  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,587 Silver Crown

    Oracle version? There could be solutions that are available or not based on your version, never forget to include that information.

    You can do a recursive query, but will have to somehow tell when to stop (you do not want to keep adding rows forever....)


    Please explain more in detail your problem, include the table definition. I guess you have a row with ID = 0 that is your "starting" date.

  • Christoph_Holzapfel
    Christoph_Holzapfel Member Posts: 10 Blue Ribbon

    Hi, @User_JP93M

    apart from the oracle version: What exactly do you want to be done automatically? Who or what defines the 'starting date' and the 'frequency'?

  • BluShadow
    BluShadow Member, Moderator Posts: 41,383 Red Diamond


    Based on what you say you want... are you looking for something along these lines...

    create sequence myPk;
    create table myTable (some_id number, starting_date date, frequency number);
    create trigger trgMyTable before insert on myTable
    for each row
    begin
      :new.some_id = myPk.nextval;
    end;
    /
    insert into mytable(starting_date, frequency) values (date '2021-01-04', 14);
    commit;
    create or replace procedure mytable_check is
    begin
      insert into myTable (starting_date, frequency)
        select trunc(sysdate)
              ,frequency
        from   myTable
        where  starting_date+frequency = trunc(sysdate);
      commit; 
    end;
    /
    -- create a scheduled job that runs daily to call the procedure
      
    


    This would then take any rows in the table where the starting_date+frequency is the current day and then insert a new row for the current day with the same frequency. Having that scheduled job run daily will then continue to create rows at the required frequencies ad-infinitum.