Oracle: INSERT ALL statement

Once the Table is Created, We need to insert the values in the Database as we have discussed earlier in the Insert statement of oracle. Through the Insert statement, we insert data by inserting rows in a single table at a time.

However many times there occurs a lot more data that needs to be inserted in different Tables. Inserting multiple rows one by one will consume a lot of time. To resolve this problem we can use the INSERT ALL Statement in Oracle. 

INSERT ALL Statement in Oracle is used to Insert values in multiple Tables in a single Code.

Syntax

INSERT ALL 
INTO Table1(Col1, Col2, Col3...) VALUES(Val1, Val2, Val3...)
INTO Table2(Col1, Col2, Col3...) VALUES(Val1, Val2, Val3..)
SELECT * from Dual;

1. Table1, Table2 represents the name of the Table in which the data needs to be inserted.
2. Col1, Col2, Col3.. Represents columns of the Table
3. Val1, Val2, Val3.. represents the Values that need to be inserted in the Column.

Example

We want to add some data in two different Tables Student and Children. And, we have Student Table as follows:

Refer to the Children Table below:

Now, to Insert Values:

INSERT ALL 
INTO Student(Name,Section,Roll_No,Subject) VALUES('Madhu','A',30,'Art')
INTO Children(Name,Section,Roll_No,Subject) VALUES('Maya','A',90,'Art')
SELECT * from Dual;

Output:

Below find how Student Table and Children Table look after execution of the above code: