Oracle INSERT into two tables in one query

Question

Just wondering if it is possible to run an INSERT into two tables in a single query for Oracle 11g?

I know you can do a INSERT ALL ... SELECT query, but I need to do this without the SELECT as this is data coming straight from XLS into the database.

ideally I'd want something like this example:

INSERT INTO table1 t1, table2 t2 
(t1.tid, t1.date, t1.title, t2.tid, t2.date, t2.user, t2.note)
VALUES (1,'01-JAN-15','title',1,'01-JAN-15','john','test note');

Any ideas?

I don't think this is possible. Just perform two inserts for each row in the Excel sheet. If you need this to be atomic, you can use a transaction. — GolezTrol
– GolezTrol, Commented Apr 25, 2014 at 14:55
Yeah the 2 INSERTS per entry seems to easy option, but as there will be apx 5,000 entries, I was just wondering if i could stick to 5,000 INSERTS rather than 10,000. But it is a one-off job (hopefully) so if it can't be done then so be it. — KS1
– KS1, Commented Apr 25, 2014 at 14:58

tjati · Accepted Answer · 2014-04-25 14:57:54Z

34

Try to use from dual;, like this:

INSERT ALL
INTO table1
  (tid, date, title) values (s_tid, s_date, s_title)
INTO table2
  (tid, date, user, note) values (s_tid, s_date, s_user, s_note)
SELECT s_tid, s_date, s_title, s_user, s_note
FROM
( 
    SELECT 
        1 s_tid,
        '01-JAN-15' s_date,
        'title' s_title,
        'john' s_user,
        'test note' s_note
    FROM dual;
)

answered Apr 25, 2014 at 14:57

tjati

6,1366 gold badges47 silver badges62 bronze badges

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

2 Comments

KS1 Over a year ago

Interesting, so the INSERT ALL...SELECT but I provide the SELECT data. It works (slightly long query), but it's still inserting into two tables in a single query. Cheers!

Pat Jones Over a year ago

This worked great for me even with a more complicated structure after the INSERT clauses: WITH cte AS ( ... ) SELECT ... FROM cte

Viktorov · Accepted Answer · 2020-06-08 08:10:25Z

-5

INSERT ALL
INTO table1
  (tid, curr_date, title) values (s_tid, s_date, s_title)
INTO table2
  (tid, curr_date, sys_user, note) values (s_tid, s_date, s_user, s_note)
SELECT s_tid, s_date, s_title, s_user, s_note
FROM
( 
    SELECT 
        2 s_tid,
        '01-FEB-15' s_date,
        'java' s_title,
        'Bablu Gope' s_user,
        'java_note' s_note
    FROM dual);

process to execute the above query.

copy the query into a file whose extension must be .sql like test.sql
connect to database
run this command
@test.sql

edited Jun 8, 2020 at 8:10

Viktorov

1362 silver badges8 bronze badges

answered Sep 22, 2019 at 10:25

Bablu Gope

93 bronze badges

Collectives™ on Stack Overflow

Oracle INSERT into two tables in one query

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related