syntax and example of insert into statement in oracle plsql

The INSERT statement in Oracle SQL / PLSQL allows us to insert record(s)/row(s) in a table.

Syntax for the INSERT statement in Oracle SQL / PLSQL is:

INSERT INTO table_name
(column_name1
,column_name2
,column_name3
.
.
column_nameN)
VALUES
(value1
,value2
,value3
.
.
valueN);

Let’s take an example for understanding:
Suppose we have a table named ’employee’ in the database as shown below.

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support

Scenario 1:

If we want to add one more row to the employee table we use the INSERT INTO statement as follows:

INSERT INTO employee
(employee_id
,employee_name
,salary
,department
,commission)
VALUES
(105
,'EMP E'
,32000
,'Sales'
,10);

The above statement will insert a new record in the table ‘employee’.

Now if we query the ‘employee’ table as;

SELECT *
FROM employee;

We will get the following result:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Now we can see that a record with ‘employee_id’ = 105 has been successfully inserted in to the ‘employee’ table.

Scenario 2:

If we want to add row(s) from an already existing table:
Let’s assume that we have created a new table namely ‘employee_data’ using the code shown below:

CREATE TABLE employee_data
(emp_id   NUMBER(20)
,emp_name VARCHAR2(300)
);

If we query the ’employee_data’ table as:

SELECT *
FROM employee_data;

We will get the following result:

Emp_Id

Emp_Name

The table will not be having any records.
Suppose we want the ‘employee_data’ table to store the ‘emp_id’ and ‘emp_name’ of all the employees whose salary is more than 21000.
We can achieve the same as:

INSERT INTO employee_data
(emp_id
,emp_name)
(SELECT employee_id
        ,employee_name
 FROM employee
 WHERE salary >= 21000);

Once we have run the above code we will query the ‘employee_data’ table and following will be the result:

Emp_ID	Emp_Name
103	Emp C
104	Emp D
105	Emp E

Here we have successfully inserted 3 records from ‘employee’ table into ‘employee_data’ table in one shot.