SQL Insert Into with data from a Select-CodePudding

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!

CodePudding user response：

You can use merge. Basically it does exactly what you need.

Edit:

Merge statement requires target data and source data. Here target data will be the table which you want to insert data (skip if already exists). And source will be the data from another table or from client request. There condition in merge statement like when matched, when NOT MATCHED BY TARGET, etc. So in these conditions you can write your logic such as insert into table b ..... in when not matched condition and so on..

CodePudding user response：

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;
/