How to insert additional records based of another

User_JP93M

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

Frank Kulash

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

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

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

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

https://community.oracle.com/tech/developers/discussion/4481612/how-to-insert-additional-records-based-of-another

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.