Oracle INSERT Statement with Examples

INSERT statement in Oracle is used to insert data into the table. It comes under DML ( Data Manipulation Language ) Commands in Oracle.

Syntax:

The Syntax of INSERT Oracle statement is as follows:

INSERT INTO table_name (column1, column2,..)
VALUES( value1, value2, ..);

Here,

table_name : It is the name of table in which we want to insert values.

column1, column2.. : Here, we specify the list of columns in which we want to insert values. We can also skip the column list altogether but its not a good practice.

value1, value2… : Here, we specify the list of values which we want to insert in columns we specified. It’s important to keep the list of values in the same order as we kept columns.

If we want to exclude more than one columns then mentioning the column list is mandatory as Oracle uses this column list to match and insert values into respective column. The column that is omitted from the list uses the default value to fill that row, if default value exists or a NULL value in case the default value is absent.

Oracle INSERT Examples

Let’s create a sample table, which we will use for inserting the values.

CREATE TABLE interns (
    intern_id NUMBER PRIMARY KEY,
    intern_name VARCHAR2(255) NOT NULL,
    salary NUMBER NOT NULL,
    location_name VARCHAR2(255) NOT NULL,
    hire_date DATE NOT NULL
);

Here, we used this query to create a new table INTERNS and here we will try to insert values into each of these columns.

1. Basic Oracle INSERT Example

Let’s see the first example, where we use INSERT statement to insert rows into the first table.

INSERT INTO 
interns(intern_id, intern_name,salary,location_name,hire_date)
VALUES(10,'Robin',2000,'New York', DATE '2020-06-14');

In this statement, we used date literals for hire_date column.

Let’s check the table using SELECT query,

SELECT * FROM interns;

Result:

Let’s insert one more row into the table,

INSERT INTO 
interns(intern_id, intern_name,salary,location_name,hire_date)
VALUES(20,'Jennifer',2000,'Sydney', SYSDATE);

Here we use SYSDATE function to insert the system date into the table.

SELECT * FROM interns;

Result:

2. Inserting Rows without specifying Column name

We can insert rows in a table without specifying the column name, if the following conditions are met:

1. Values must be provided for all columns in the table
2. Values are given in default order of the columns as defined in table structure while creating table.

Let’s see one query:

INSERT INTO 
interns VALUES(30,'Trump',7000,'New York', DATE '2020-08-14');

Result:

As we can see, the new intern with intern_id=30 has been inserted into the table.

3. Inserting DATE data types

We have inserted dates in the table above, but if we want to insert dates which are in text format then we first need to convert them to date using TO DATE function.

Let’s have a look at one query:

INSERT INTO interns
VALUES(40,'Batman',5000,'Gotham', TO_DATE( 'FEB 13, 2020', 'MON DD, YYYY'));

Result:

The new intern named ‘Batman’ has been inserted into the table.

4. Insert rows from another table

Suppose there are two tables with similar structures and column names then, we can use a subquery to directly insert rows from one table to other.

Let’s see a query:

INSERT INTO Juniors
SELECT * from interns ;

Result:

We created a second table named juniors which has the same table structure as Interns and we used this query to copy all data from Interns to Juniors.

Note:

One needs to keep in mind some of the following points while Inserting data into table to avoid common errors:

• If we fail to give value for column which has NOT NULL constraint
• If we insert duplicate values in columns having UNIQUE or PRIMARY KEY constraint
• If we insert value that violates CHECK constraint
• If there is mismatch in data type or value too large
• If there is a violation of referential integrity constraint

In this article, we learned how to use Oracle INSERT statement to insert values.