How to INSERT row into table

The INSERT statement is one of the syntax of DML DATA MANIPULATION LANGUAGE. The INSERT statement is used to insert a single record or multiple records into a table in Oracle. When inserting records into a table, value should be provided for every not null column. Also we can avoid a column from the INSERT statement if the column allows NULL values.

Syntax

INSERT INTO table [(column, column...)]

VALUES (value, value...);

Example

Let us take students table for reference.

select * from students

As we can see currently the table is empty and not a single row present.

Now we will insert one record into the students table by below SQL query.

INSERT INTO students (FirstName, LastName, RollNo, Address, City)

VALUES ('Rachel','Green','21','A4 central perk','Ottawa');

COMMIT;

select * from students

We can see row for Rachel Green has been inserted in the students table.

INSERT ALL SQL Statement

The INSERT ALL statement is used to add multiple rows with a single INSERT statement. The rows can be inserted into one table or multiple tables using only one SQL command.

Example

We will now insert 5 records into students table using INSERT ALL statement as shown below:

INSERT ALL

into students ( firstname, lastname , rollno, address, city ) 

values ('Oliver','Queen',04,'1501P Avenue','Starling')

into students ( firstname, lastname , rollno, address, city ) 

values ('Barry','Allen',45,'701 Madison Building','Central')

into students ( firstname, lastname , rollno, address, city ) 

values ('Kara','Danvers',14,'108 Times Square','National')

into students ( firstname, lastname , rollno, address, city ) 

values ('Leonard','Snart',12,'201 B Prospect Plaza','Central')

into students ( firstname, lastname , rollno, address, city ) 

values ('Bruce','Wayne',13,'Star Mansion','Gotham')

select * from dual;

COMMIT;

select * from students

All the records are successfully inserted in the students table.