INSERT MULTIPLE ROWS IN TABLE USING INSERT ALL ORACLE

Use ‘insert all’ to insert multiple rows in a table at same time

Create a test table

SQL> create table test (name varchar2(10),num number(10));

Table created.

Insert multiple rows in one table at same time using insert all. Otherwise you would have to use insert statement for individual statements multiple times

SQL>  insert all into test  values('xyz',5)
  2  into test  values('abc',6)
  3  into test  values('mno',7)
  4  into test  values('nhc',8)
  5  into test  values('tnc',9)
  6  into test  values('jmk',10)
  7  select * from dual;

6 rows created.

SQL> commit;

Commit complete.

Select the rows for verification

SQL> select * from test where num not in (1,2);

NAME              NUM
---------- ----------
mno                 3
nhc                 4
xyz                 5
abc                 6
mno                 7
nhc                 8
tnc                 9
jmk                10

8 rows selected.

To insert multiple rows into multiple tables

SQL> insert all into test  values('xyz',5)
  2  into test  values('abc',6)
  3  into test1 values('mno',7)
  4  select * from dual;

3 rows created.

SQL> commit;

Commit complete.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s