Oracle Insert Statement

In Oracle, INSERT statement is used to add a single record or multiple records into the table.

Syntax: (Inserting a single record using the Values keyword):

INSERT INTO table  

(column1, column2, ... column_n )  

VALUES  

(expression1, expression2, ... expression_n );  

Syntax: (Inserting multiple records using a SELECT statement):

INSERT INTO table  

(column1, column2, ... column_n )  

SELECT expression1, expression2, ... expression_n  

FROM source_table  

WHERE conditions;  

Parameters:

1) table: The table to insert the records into.

2) column1, column2, … column_n:

The columns in the table to insert values.

3) expression1, expression2, … expression_n:

The values to assign to the columns in the table. So column1 would be assigned the value of expression1, column2 would be assigned the value of expression2, and so on.

4) source_table:

The source table when inserting data from another table.

5) conditions:

The conditions that must be met for the records to be inserted.

Oracle Insert Example: By VALUE keyword

It is the simplest way to insert elements to a database by using VALUE keyword.

See this example:

Consider here the already created suppliers table. Add a new row where the value of supplier_id is 23 and supplier_name is Flipkart.See this example:

  INSERT INTO suppliers  

(supplier_id, supplier_name)  

VALUES  

(50, 'Flipkart');  

Output:

1 row(s) inserted.
0.02 seconds

Oracle Insert Example: By SELECT statement

This method is used for more complicated cases of insertion. In this method insertion is done by SELECT statement. This method is used to insert multiple elements.See this example:

In this method, we insert values to the “suppliers” table from “customers” table. Both tables are already created with their respective columns.Execute this query:

INSERT INTO suppliers  

(supplier_id, supplier_name)  

SELECT age, address  

FROM customers  

WHERE age > 20; 

Output:

4 row(s) inserted.
0.00 seconds

You can even check the number of rows that you want to insert by following statement:

SELECT count(*)  

FROM customers  

WHERE age > 20;  

Output:

     Count(*)
     4