How to insert multiple rows in the same table-Oracle 10g How to insert multiple rows in the same table-Oracle 10g oracle oracle

How to insert multiple rows in the same table-Oracle 10g


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

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

Or you could do an 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 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.