CodesJava

Easy learning with example program codes

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:

STUDENT_ID	STUDENT_NAME	STUDENT_AGE
1	        Joy	        5
2	        Smiley	        13

Query:

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

Output:

1 row(s) inserted.
0.01 seconds

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:

STUDENT_ID	STUDENT_NAME	STUDENT_AGE
1	        Joy	        5
2	        Smiley	        13
3	        Happy	        11

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:

STUDENT_ID	STUDENT_NAME	STUDENT_AGE
1	        Joy	        5
2	        Smiley	        13

Children table:

ID	NAME	AGE
10	James	4
20	Bond	2
30	Happy	11

Query:

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

Output:

10 row(s) inserted.
0.00 seconds

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:

STUDENT_ID	STUDENT_NAME	STUDENT_AGE
1	        Joy	        5
2	        Smiley	        13
30	        Happy	        11
Please follow and like us:
error









Copyright © 2019 CodesJava DMCA.com Protection Status