如何在同一张表中插入多行-Oracle 10g [英] How to insert multiple rows in the same table-Oracle 10g

问题描述

我在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));

但这显示了错误:

insert into t1 values((131309,'HP','20-FEB-04',2000000,1235),
                             *
ERROR at line 1:
ORA-00907: missing right parenthesis

我该如何纠正?

推荐答案

INSERT VALUES语句始终精确插入1行.如果要插入带有硬编码值的多行，最常见的方法就是执行两个单独的INSERT语句.

An INSERT VALUES statement 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 INSERT statements.

insert into t1 values(131309,'HP','20-FEB-04',2000000,1235);
insert into t1 values(131310,'HT','20-APR-14',120020,1234);

如果您确实愿意，可以从dual中选择您的硬编码值，然后执行INSERT SELECT

If you really wanted to, you could select your hard-coded values from dual and then do an 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

或者您可以执行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

• 始终总是列出insert语句中的列.您将使SQL更加健壮，这样，如果将来添加允许NULL值的新列，您的语句仍然可以使用.而且，当列列表在那里时，您将避免很多错误，而不是希望有人记住表中列的顺序.
• 如果要在date列中插入值，请使用日期，而不要使用表示日期的字符串文字.依赖隐式数据类型转换是许多错误的根源.使用显式的to_date或使用ANSI日期文字.并使用4位数字的年份.

• Always, always list out the columns in your insert statement. You'll make your SQL much more robust so that if you add new columns in the future that allow NULL values 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 date column, 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 explicit to_date or use ANSI date literals. And use 4-digit years.

这篇关于如何在同一张表中插入多行-Oracle 10g的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！