SQL Oracle 11G 中的 INSERT SELECT 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7323407/
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
INSERT SELECT statement in Oracle 11G
提问by Brian
I'm trying to run a very simple sql statement in Oracle 11g.
我正在尝试在 Oracle 11g 中运行一个非常简单的 sql 语句。
insert into table1 (col1, col2) values (select t1.col1, t2.col2 from oldtable1 t1, oldtable2 t2);
Very simple query. Cartesian join old table 1 to old table 2, put the resulting values into table 1.
非常简单的查询。笛卡尔将旧表 1 连接到旧表 2，将结果值放入表 1。
I've run the subquery by itself, and it works perfectly.
我自己运行了子查询，它运行良好。
select t1.col1, t2.col2 from oldtable1 t1, oldtable2 t2
When I try to run the full statement, I get the following error:
当我尝试运行完整语句时，出现以下错误：
SQL Error: ORA-00936: missing expression
00936. 00000 - "missing expression"
I can't get it to work in MySql either. Something is wrong with my statement, but I'm not sure what it is.
我也不能让它在 MySql 中工作。我的陈述有问题，但我不确定它是什么。
回答by Ryan
Your query should be:
您的查询应该是：
insert into table1 (col1, col2)
select t1.col1, t2.col2
from oldtable1 t1, oldtable2 t2
I.e. without the VALUESpart.
即没有VALUES部分。
回答by cwallenpoole
Get rid of the valueskeyword and the parens. You can see an example here.
去掉values关键字和括号。您可以在此处查看示例。
This is basic INSERT syntax:
这是基本的 INSERT 语法：
INSERT INTO "table_name" ("column1", "column2", ...)
VALUES ("value1", "value2", ...);
This is the INSERT SELECT syntax:
这是 INSERT SELECT 语法：
INSERT INTO "table1" ("column1", "column2", ...)
SELECT "column3", "column4", ...
FROM "table2";
回答by Chains
You don't need the 'values' clause when using a 'select' as your source.
使用“select”作为源时，不需要“values”子句。
insert into table1 (col1, col2)
select t1.col1, t2.col2 from oldtable1 t1, oldtable2 t2;
回答by sudarshan
for inserting data into table you can write
要将数据插入表中，您可以编写
insert into tablename values(column_name1,column_name2,column_name3);
but write the column_namein the sequence as per sequence in table ...
但是column_name按照表中的顺序按顺序写...
回答by sudarshan
There is an another option to insert data into table ..
还有另一个选项可以将数据插入表中..
insert into tablename values(&column_name1,&column_name2,&column_name3);
it will open another window for inserting the data value..
它将打开另一个窗口以插入数据值..
