Skip to content

How to Insert Multiple Rows in One Statement

  • by

Multiple Rows Insert

To insert rows into a table, you can do it one by one.

insert into fruits values ('Apple', 2);
insert into fruits values ('Banana', 1);
insert into fruits values ('Cherry', 5);

We can also insert multi-row data in a statement.

insert into fruits
    select 'Apple', 2 from dual
        union all
    select 'Banana', 1 from dual
        union all
    select 'Cherry', 5 from dual
;

In the above statement, we use SELECT DUAL to collect values and use UNION ALL to pack them into one set before INSERT INTO.

Let's see the result.

SQL> select * from fruits;

FRUIT_NAME           PRICE_PER_LB
-------------------- ------------
Apple                           2
Banana                          1
Cherry                          5

As for INSERT ALL statement, it should be used and suitable for multi-table data insert.

Leave a Reply

Your email address will not be published.