INSERT query in Oracle

ORACLE INSERT To insert a single record or multiple records into a table the Oracle INSERT statement is used.

Oracle INSERT by using the VALUE keyword: Inserting elements to the Oracle database by using the VALUE keyword is the simplest way of insertion in Oracle.

Syntax:

INSERT into table_name(column_1, column_2, ... column_n )  
VALUES(value1, value2, .. valuen);  

Parameters: table_name: It is used to specify the name of the table in which the records need to be inserted. column_1, column_2, … column_n: It is used to specify the columns of the table in which the values needs to be inserted. value1, value2, … valuen: It is used to specify the values to be inserted to the respective columns. For example, value1 will be inserted into column_1, value2 to column_2 and so on.

Example: Students table before insertion:

Query:

INSERT INTO students  
(student_id, student_name, student_age)  
VALUES  
(3, 'Happy’, 11');

Output:

1 row(s) inserted.

Explanation: The ‘students’ is an already created table. Here we are adding a new row under student_id, student_name and student_age with the corresponding values: 3, ‘Happy’ and 11. So the student table after insertion will become,

Students table after insertion:

Oracle Insert Example by using the SELECT statement: Inserting elements to the Oracle database by using the SELECT statement is used for more complicated cases of insertion, usually to insert multiple elements.

Syntax:

INSERT INTO table_name  
(column_1, column_2, ... column_n )  
SELECT expression_1, expression_2, ... expression_n  
FROM base_table  
WHERE conditions;   

Parameters: table_name: It is used to specify the name of the table in which the records need to be inserted. column_1, column_2, … column_n: It is used to specify the columns of the table in which the values needs to be inserted. expression_1, expression_2, … expression_n: It is used to specify the values to be inserted to the respective columns. For example, expression_1 will be inserted into column_1, expression_2 to column_2 and so on. base_table: It is used to specify the base table which is another table from which data is to be inserted to the desired table. conditions: It is used to specify the conditions to be strictly followed for selection.

Example: Students table before insertion:

Children table:

Query:

INSERT INTO students  
(student_id, student_name, student_age)  
SELECT id, name, age  
FROM children  
WHERE age > 5;

Output:

1 row(s) inserted.

Explanation: There are two already created tables, namely, ‘students’ and ‘children’. Here, we are inserting values from the “children” table to the “students” table. So the student table after insertion will become,

Students table after insertion:

Please follow and like us: