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!

Does this answer your question? [Oracle: how to UPSERT (update or insert into a table?)](https://stackoverflow.com/questions/237327/oracle-how-to-upsert-update-or-insert-into-a-table) — astentx, Oct 06 '22 at 09:41
You may also use `not in`/`not exists` predicate to filter out existing records. — astentx, Oct 06 '22 at 09:43

score 2 · Answer 1 · edited Oct 06 '22 at 09:44

2

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..

edited Oct 06 '22 at 09:44

MT0

143,790
11
59
117

answered Oct 06 '22 at 09:22

Vivek Rahul

314
2
15

1

Please [edit] this answer to explain to the OP how `MERGE` would be used to answer the question. As it stands, this is more of a comment than an answer. – MT0 Oct 06 '22 at 09:24

Littlefoot · Accepted Answer · 2022-10-06T10:59:53.510

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

The initial statement expanded with your `not_exists` clause returns "0 rows inserted" with my data. But there are 2 new rows, which should be inserted The condition `x.id<>a.id` is given with these new rows. Do you have an idea, what the problem could be? — schmimla, Oct 06 '22 at 10:54
If I understood correctly what you're trying to do, change the 'table_a x' in the sub query to 'b x' — gsalem, Oct 06 '22 at 10:57

SQL Insert Into with data from a Select

2 Answers2