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?) — astentx
– astentx, Commented Oct 6, 2022 at 9:41
You may also use not in/not exists predicate to filter out existing records. — astentx
– astentx, Commented Oct 6, 2022 at 9:43

MT0 · Accepted Answer · 2022-10-06 09:44:34Z

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 6, 2022 at 9:44

MT0

173k12 gold badges70 silver badges136 bronze badges

answered Oct 6, 2022 at 9:22

Vivek Rahul

372 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MT0 Over a year ago

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.

Littlefoot · Accepted Answer · 2022-10-06 10:59:53Z

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

edited Oct 6, 2022 at 10:59

answered Oct 6, 2022 at 9:24

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

4 Comments

schmimla Over a year ago

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?

gsalem Over a year ago

If I understood correctly what you're trying to do, change the 'table_a x' in the sub query to 'b x'

Littlefoot Over a year ago

I'd say you're right, @gsalem. Fixed, thank you.

schmimla Over a year ago

Yeah, that works. Thank you vermy much!

Collectives™ on Stack Overflow

SQL Insert Into with data from a Select

2 Answers 2

1 Comment

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related