Insert statement in Oracle is used to insert a single or multiple records in the table.
There are two ways to perform an insertion of record/records in Oracle.
- Direct Insert into the table
- Insert into one table using other table
Below are the syntax and examples of both the kind of Insert statements.
- Direct insert into the table
INSERT INTO [schema_name.]table_name (column_1, column_2...column_n) VALUES (value_1, value_2...value_n);
- Insert from one table to another table
INSERT INTO [schema_name.]table_name (column_1, column_2...column_n) SELECT column_1, column_2...column_n FROM source_table WHERE condition_1 AND condition_2;
- Name of the schema to which the table belongs. It is optional.
- Name of the table in which data is to be inserted. It is mandatory.
- Table from data is to be inserted into destination table.
- Columns of the table into which the data is required to be inserted.
- Conditions used to filter data while fetching from the table.
If you do not mention the column names then make sure you have entered the values of all the columns of the table in VALUES clause.
Conditions in AND and WHERE clause are optional.
You can also perform calculations before entering value into destination table.
Examples of Insert statement
- Direct insert using specific column names
INSERT INTO employees (emp_id, emp_name) VALUES (1001, 'John Doe');
- Direct insert for all columns
INSERT INTO employees VALUES (1001, 'John Doe', 'New York');
In both the examples above, table employees have three columns employee id, employee name and city.
- Insert records from other table
INSERT INTO emp_usa(emp_id, emp_name) SELECT emp_id, emp_name FROM employees WHERE country = 'United States';
In the above example, the employees of country United States are entered into emp_usa table.
Imagine the time it takes to move more than 10000 records of USA employees using normal insert. You would have needed to create insert statement for all the records separately.
- Insert records using calculation
INSERT INTO emp_hike(emp_id, salary) SELECT emp_id, salary + hike_percentage FROM employees;
The above example inserts hiked salary of all employees into emp_hike table.
I hope you like the page and find it useful. We highly appreciate comments and feedback.
Hi I am Paras.
Thanks for stopping by at OracleMine.com. Speaking about my brief introduction, I work for a multinational organisation in Oracle related technologies. Being an avid blogger, I would like to inform you about my productivity and motivational blog XpressPlanet.com. Speaking of OracleMine.com, I will try my best to share knowledge on technologies in as simple and understandable manner as possible. You can also contribute your knowledge on OracleMine by writing to us at firstname.lastname@example.org. Again I appreciate your visit. Hope to see you again and again!