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.