Oracle SQL - Data Change Command INSERT Command

Introduction

You use the INSERT command to add rows to a table.

The standard INSERT command supports the following two ways to insert rows:

• Use the VALUES clause, followed by a list of column values (between parentheses). This method allows you to insert only one row at a time per execution of the INSERT command.
• Formulate a subquery, thus using existing data to generate new rows.

If you know all of the table columns and their internal physical order in DESCRIBE command, you don't need to specify column names after the table name in the INSERT command.

If you omit column names, you must provide precisely enough values and specify them in the correct order.

In the VALUES clause, you can specify a comma-separated list of literals or an expression.

You can use the reserved word NULL to specify a null value for a specific column.

You can specify the reserved word DEFAULT to instruct the Oracle DBMS to insert the default value associated with the corresponding column.

These default values are part of the table definition, stored in the data dictionary.

If you don't specify a value for a specific column in your INSERT statement, there are two possibilities:

• If the column has an associated DEFAULT value, the Oracle DBMS will insert that value.
• If you did not define a DEFAULT value for the column, the Oracle DBMS inserts a null value if the column allows null values.

The second way of using the INSERT command is to fill a table with a subquery.

These subqueries should produce the right number of values of the right data type.

Related Topics

• Using INSERT statement with NULL
• Using INSERT statement with DEFAULT
• A violation of a primary key constraint in INSERT statement
• Using a subquery to insert rows with the INSERT
• INSERT Using Subqueries

Quiz

• Write SQL to fill the new SALGRADES2 table with a single INSERT statement, using the existing SALGRADES table.

• Previous
• Next