如何在同一个表中插入多行-Oracle 10g
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25679935/
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
How to insert multiple rows in the same table-Oracle 10g
提问by h8pathak
I created a table in Oracle SQL:
我在以下位置创建了一个表Oracle SQL：
create table t1
(
empno number(6) PRIMARY KEY,
empname varchar(30),
hiredate date,
basic number(8),
deptno number(4)
);
And now I am inserting values into the table using a single query:
现在我使用单个查询将值插入表中：
insert into t1 values((131309,'HP','20-FEB-04',2000000,1235)
(131310,'HT','20-APR-14',120020,1234));
But this shows error:
但这显示错误：
insert into t1 values((131309,'HP','20-FEB-04',2000000,1235),
*
ERROR at line 1:
ORA-00907: missing right parenthesis
How do I correct this?
我该如何纠正？
回答by Justin Cave
An INSERT VALUESstatement always inserts exactly 1 row. If you want to insert multiple rows with hard-coded values, the most common approach would simply be to execute two separate INSERTstatements.
一条INSERT VALUES语句总是恰好插入 1 行。如果要插入具有硬编码值的多行，最常见的方法就是执行两个单独的INSERT语句。
insert into t1 values(131309,'HP','20-FEB-04',2000000,1235);
insert into t1 values(131310,'HT','20-APR-14',120020,1234);
If you really wanted to, you could select your hard-coded values from dualand then do an INSERT SELECT
如果您真的想要，您可以从中选择您的硬编码值dual，然后执行INSERT SELECT
insert into t1
select 131309, 'HP', '20-FEB-04',2000000,1235 from dual
union all
select 131310,'HT','20-APR-14',120020,1234 from dual
Or you could do an INSERT ALL
或者你可以做一个 INSERT ALL
insert all
into t1 values(131309,'HP','20-FEB-04',2000000,1235)
into t1 values(131310,'HT','20-APR-14',120020,1234)
select * from dual
Personally, I'd just use two statements.
就个人而言，我只会使用两个语句。
Although this isn't related to your question, a couple of comments
虽然这与您的问题无关，但有一些评论
- Always, always list out the columns in your
insertstatement. You'll make your SQL much more robust so that if you add new columns in the future that allowNULLvalues your statements will still work. And you'll avoid lots of bugs when the column list is right there rather than hoping that someone remembers the order of columns in the table. - If you're inserting a value into a
datecolumn, use a date not a string literal that represents a date. Relying on implicit data type conversion is a source of many bugs. Use an explicitto_dateor use ANSI date literals. And use 4-digit years.
- 始终，始终列出
insert语句中的列。您将使您的 SQL 更加健壮，这样如果您将来添加允许NULL值的新列，您的语句仍然可以工作。当列列表就在那里而不是希望有人记住表中列的顺序时，您将避免很多错误。 - 如果要向
date列中插入值，请使用日期而不是表示日期的字符串文字。依赖隐式数据类型转换是许多错误的来源。使用显式to_date或使用 ANSI 日期文字。并使用 4 位数年份。
