Oracle INSERT

Summary: in this tutorial, you will learn how to use the Oracle INSERT statement to insert data into a table.

Introduction to Oracle INSERT statement

To insert a new row into a table, you use the Oracle INSERT statement as follows:

INSERT INTO table_name (column_list)
VALUES( value_list);
Code language: SQL (Structured Query Language) (sql)

In this statement:

• First, specify the name of the table into which you want to insert.
• Second, specify a list of comma-separated column names within parentheses.
• Third, specify a list of comma-separated values that correspond to the column list.

If the value list has the same order as the table columns, you can skip the column list although this is not considered a good practice:

INSERT INTO table_name
VALUES (value_list);
Code language: SQL (Structured Query Language) (sql)

If you exclude one or more columns from the Oracle INSERT statement, then you must specify the column list because Oracle needs it to match with values in the value list.

The column that you omit in the INSERT statement will use the default value if available or a NULL value if the column accepts a NULL value.

Oracle INSERT statement examples

Let’s create a new table named discounts for inserting data:

CREATE TABLE discounts (
    discount_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    discount_name VARCHAR2(255) NOT NULL,
    amount NUMBER(3,1) NOT NULL,
    start_date DATE NOT NULL,
    expired_date DATE NOT NULL
);
Code language: SQL (Structured Query Language) (sql)

In the discounts table, the discount_id column is an identity column whose default value is automatically generated by the system, therefore, you don’t have to specify the discount_id column in the INSERT statement.

The other columns discount_name, amount, start_date, and expired_dateare the NOT NULL columns, so you must supply the values for them.

The following statement inserts a new row into the discounts table:

INSERT INTO discounts(discount_name, amount, start_date, expired_date)
VALUES('Summer Promotion', 9.5, DATE '2017-05-01', DATE '2017-08-31');
Code language: SQL (Structured Query Language) (sql)

In this statement, we used the date literals DATE '2017-05-01' and DATE '2017-08-31' for the date columns start_date and expired_date.

The following statement retrieves data from the discounts table to verify the insertion:

SELECT
    *
FROM
    discounts;
Code language: SQL (Structured Query Language) (sql)

The following example inserts a new row into the discounts table:

INSERT INTO discounts(discount_name, amount, start_date, expired_date)
VALUES('Winter Promotion 2017',  10.5, CURRENT_DATE, DATE '2017-12-31');
Code language: SQL (Structured Query Language) (sql)

In this example, instead of using the date literal, we used the result of the CURRENT_DATE function for the start_date column.

See the following result:

SELECT
    *
FROM
    discounts;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the Oracle INSERT statement to insert a new row into a table.