Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle INSERT into two tables in one query

Tags:

sql

oracle

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?

like image 576
KS1 Avatar asked Apr 25 '14 14:04

KS1


People also ask

Can I insert into two tables at once SQL?

You can not insert data into 2 tables simultaneously in a single session. But if u split the insert statements into 2 statements, it is going to give you the same effect! But make sure to add ORDER by in your SELECT statement for both the inserts.

How do I insert data into multiple tables at once?

No, you can't insert into multiple tables in one MySQL command. You can however use transactions. BEGIN; INSERT INTO users (username, password) VALUES('test', 'test'); INSERT INTO profiles (userid, bio, homepage) VALUES(LAST_INSERT_ID(),'Hello world!

What is multi table insert in Oracle?

Multitable inserts were introduced in Oracle 9i to allow a single INSERT INTO .. SELECT statement to conditionally, or unconditionally, insert into multiple tables. This statement reduces table scans and PL/SQL code necessary for performing multiple conditional inserts compared to previous versions.


1 Answers

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;
)
like image 157
tjati Avatar answered Oct 23 '22 11:10

tjati