SQL Oracle 在一个查询中插入两个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23296432/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle INSERT into two tables in one query
提问by KS1
Just wondering if it is possible to run an INSERT into two tables in a single query for Oracle 11g?
只是想知道是否可以在 Oracle 11g 的单个查询中将 INSERT 运行到两个表中？
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.
我知道您可以执行 INSERT ALL ... SELECT 查询，但我需要在没有 SELECT 的情况下执行此操作，因为这是直接从 XLS 进入数据库的数据。
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?
有任何想法吗？
回答by tjati
Try to use from dual;, like this:
尝试使用from dual;，像这样：
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;
)
回答by Bablu Gope
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);
INSERT ALL INTO table1 (tid, curr_date, title) 值 (s_tid, s_date, s_title) INTO table2 (tid, curr_date, sys_user, note) 值 (s_tid, s_date, s_user, s_note) SELECT s_tid, s_date, s_user, s_title 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
执行上述查询的过程。1. 将查询复制到扩展名为 .sql 的文件中，如 test.sql 2. 连接到数据库 3. 运行此命令 4. @test.sql
