CODEWITHSUNDEEP
sqloracle-database
KS1
KS1

Reputation: 1039

Oracle INSERT into two tables in one query

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?

Upvotes: 14

Views: 36198

Answers (2)

Bablu Gope
Bablu Gope

Reputation: 9

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.

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

Upvotes: -5

tjati
tjati

Reputation: 6079

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

Upvotes: 33

Related Questions