SQL Insert Into with data from a Select

Question

I would like to insert some data collected from a query into a table which I have created to hold up only this specific data.

This is the initial statement (database is oracle):

insert into table b
(select a.id, a.changed, a.column_name, a.identification, a.old_text, a.new_text, d.id, d.no, d.name, d.status, d.status_date 
from table_a a 
inner table_d d 
on d.id = a.id_double1
where table_name = 'DEVICE' and column_name  = 'STATUS' or column_name = 'STATUS_DATE')

This statements works(I created table b with the same columns and data types as the columns in the select). What I want to do now is to run a nightly job, which only fetches the new records into the table and "skips" the already existing records.

Can somebody explain how to do this? Thank you very much!

Answer 1

To skip rows you already inserted, include a not exists condition into the where clause.

If you want to schedule a job, "convert" that insert statement into a procedure you'd then schedule using the dbms_scheduler built-in package.

Something like this:

CREATE OR REPLACE PROCEDURE p_insert
IS
BEGIN
   INSERT INTO b
      (SELECT a.id,
              a.changed,
              a.column_name,
              a.identification,
              a.old_text,
              a.new_text,
              d.id,
              d.no,
              d.name,
              d.status,
              d.status_date
         FROM table_a a INNER JOIN table_d d ON d.id = a.id_double1
        WHERE     (       table_name = 'DEVICE'
                      AND column_name = 'STATUS'
                   OR column_name = 'STATUS_DATE')
              AND NOT EXISTS
                     (SELECT NULL
                        FROM b x
                       WHERE x.id = a.id));
END;
/

How to schedule it to run at 23:00 from Monday to Friday (that's just an example - adjust it, if needed)?

BEGIN
   DBMS_SCHEDULER.CREATE_JOB (
      job_name         => 'p_insert',
      job_type         => 'PLSQL_BLOCK',
      job_action       => 'BEGIN p_insert; end;',
      start_date       =>
         TO_TIMESTAMP_TZ ('06.10.2022 23:00 Europe/Zagreb',
                          'dd.mm.yyyy hh24:mi TZR'),
      repeat_interval  =>
         'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=23; BYMINUTE=0',
      enabled          => TRUE,
      comments         => 'Night job');
END;
/