INSERT INTO Statement in Oracle SQL – 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.


Tagged , , , , , , , , , , , . Bookmark the permalink.