oracle 我想使用 union ALL 从 select 语句中插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8323887/
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
I want to insert from a select statement using union ALL
提问by Anubhav Aeron
I want to use union on a query while inserting records in a table. The insert is also having some hard coded values. Rest of the values are coming from the union. eg..
我想在表中插入记录时对查询使用联合。插入也有一些硬编码值。其余的值来自联合。例如..
INSERT INTO my_test_one (name, sirname, Dept)
VALUES
((select 'name1','sirname1', Dept FROM my_test_one_backup WHERE dept = 500
union all
select 'name1','sirname1', Dept FROM my_test_one_backup WHERE dept = 501 ));
回答by Ollie
Try:
尝试：
INSERT INTO my_test_one (name, sirname, Dept)
(select 'name1','sirname1', Dept
FROM my_test_one_backup
WHERE dept = 500
UNION ALL
select 'name1','sirname1', Dept
FROM my_test_one_backup
WHERE dept = 501 );
Hope it helps...
希望能帮助到你...
回答by Michael Krelin - hacker
I think you need no VALUEShere. (INSERT INTO ... ( ... ) SELECT ...)
我认为你不需要VALUES这里。( INSERT INTO ... ( ... ) SELECT ...)
回答by Fred
Why don't you use two insert instead of using a union all?
为什么不使用两个 insert 而不是使用 union all？
INSERT INTO my_test_one (name, sirname, Dept)
SELECT 'name1','sirname1', Dept FROM my_test_one_backup WHERE dept = 500;
INSERT INTO my_test_one (name, sirname, Dept)
SELECT 'name1','sirname1', Dept FROM my_test_one_backup WHERE dept = 501;
or
或者
INSERT INTO my_test_one (name, sirname, Dept)
SELECT 'name1','sirname1', Dept FROM my_test_one_backup WHERE dept in (500,501);
回答by aF.
You can do it like this:
你可以这样做：
If the table doesn't exist you may use this:
如果表不存在，你可以使用这个：
SELECT 'name1' [name],'sirname1' [sirname], Dept INTO my_test_one FROM my_test_one_backup WHERE dept = 500
UNION ALL
SELECT 'name1','sirname1', Dept FROM my_test_one_backup WHERE dept = 501
Otherwise use this:
否则使用这个：
INSERT INTO my_test_one (name, sirname, Dept)
SELECT 'name1','sirname1', Dept FROM my_test_one_backup WHERE dept in (500,501)
