Insert Statement in Oracle

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.

  1. Direct Insert into the table
  2. Insert into one table using other table

Below are the syntax and examples of both the kind of Insert statements.

INSERT Statement

Syntax

  • 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;
Parameters Explanation

schema_name

  • Name of the schema to which the table belongs. It is optional.

table_name

  • Name of the table in which data is to be inserted. It is mandatory.

source_table

  • Table from data is to be inserted into destination table.

column_1, column_2…column_n

  • Columns of the table into which the data is required to be inserted.

condition_1, condition_2

  • 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.

You can also Subscribe here to stay updated on latest posts of OracleMine.com.

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 hioraclemine@gmail.com. Again I appreciate your visit. Hope to see you again and again!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.