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:
1 | INSERT INTO employee |
2 | (employee_id |
3 | ,employee_name |
4 | ,salary |
5 | ,department |
6 | ,commission) |
7 | VALUES |
8 | (105 |
9 | , 'EMP E' |
10 | ,32000 |
11 | , 'Sales' |
12 | ,10); |
The above statement will insert a new record in the table ‘employee’.
Now if we query the ‘employee’ table as;
1 | SELECT * |
2 | 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:
1 | CREATE TABLE employee_data |
2 | (emp_id NUMBER(20) |
3 | ,emp_name VARCHAR2(300) |
4 | ); |
If we query the ’employee_data’ table as:
1 | SELECT * |
2 | 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:
1 | INSERT INTO employee_data |
2 | (emp_id |
3 | ,emp_name) |
4 | ( SELECT employee_id |
5 | ,employee_name |
6 | FROM employee |
7 | 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.