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?
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.
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!
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.
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;
)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With