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 allowNULL
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 explicitto_date
or use ANSI date literals. And use 4-digit years.