The INSERT command is used to insert rows of data values (records) into an existing table.
Required Privileges
To insert rows into a table, the user authorization executing the INSERT statement must own the table, own or possess OWNER privileges on the schema to which the table belongs, possess ALL PRIVILEGES on the schema to which the table belongs, or possess DBA privileges.
If a query expression is specified in the INSERT statement, the user must possess sufficient privileges to execute that query.
Syntax
table name
Preceded by the keyword INTO, the table name argument identifies
the table affected by the INSERT statement.
column list
The column list argument specifies an optional list of columns into
which the data is to be inserted. The column list, if specified, must be enclosed
in parentheses, with each column name separated from the next by a comma.
Any column not included in the column list will have a null value inserted.
If the column list is omitted, all columns in the target table or view are
assigned values, proceeding from left to right.
value list
Preceded by the keyword VALUES, the value list argument specifies one
row of values to be inserted into the target table. The value list must be
enclosed in parentheses, with each element separated from the next by a comma.
query expression
The query expression argument defines a SELECT
statement that retrieves data from another table for insertion into the table
named in the INSERT command. The same table cannot be the base object of both
the INSERT command and the query expression (or any subquery
contained in the query expression). An INSERT statement cannot include both
a query expression and a value list.
Description
The INSERT command is used to insert rows of data values (records) into an existing table. The data to be inserted with the INSERT command can be specified by the user in the values list, or retrieved from other tables using a query expression:
- The INSERT statement using a value list inserts a single row into the table or view, using the values provided.
- The INSERT statement with a query expression inserts zero or more rows into the table or view, using values retrieved from other tables and/or views by the SELECT statement.
VALUES
The number of individual data items in the value list must
equal the number of items in the column list. The first data item in the value
list is inserted into the first column in the column list, the second value
in the second column, and so on. If the column list is omitted, the number
of data items in the value list must equal the number of columns in the target
table.
A value list must be included if a query expression is not specified. The value list is enclosed in parentheses, and each value is separated from the next by a comma. Character strings are enclosed by single quotation marks. A value list item must be compatible with the data type of the column in the target table (see Data Compatibility below). If the value to be inserted into a column is null, the keyword NULL should appear at the appropriate location in the value list. Alternatively, since NULL is the default value for all columns, a null value may be inserted without the NULL keyword by simply placing a comma in the appropriate location in the value list, with no value specified for that column.
Query Expression
If a value list is not specified for the INSERT command, a query expression (SELECT statement) must be included. The order and number of columns selected must match the order and number in the column list specification. If an ORDER BY clause is included, a FETCH FIRST...ONLY clause should be present as well, otherwise the ORDER BY clause will be ignored.
There must not be an active transaction if the INSERT...SELECT command is inserting into a partitioned table the results of a query of other partitioned and/or dimension table(s), otherwise an error will be generated. In this situation, an explicit COMMIT or ROLLBACK may be required to start a new transaction before executing the INSERT...SELECT command.
Data Compatibility
In most cases, the columns specified in the query expression do not need to match the data types of the columns in the column list. They must, however, be of comparable type:
- If the insert value for a column is a number, the column must be defined as a numeric column capable of representing the integral part of the inserted number value.
- If the insert value for a column is a string, the column must be configured as a character column, with a length greater than or equal to the string length, or a BLOB column.
- If the insert value for a column is a date, the column must be defined as a DATE column.
- If the insert value for a column is a time value, the column must be defined as a TIME column.
- If the insert value for a column is a timestamp, the column must be defined as a TIMESTAMP column.
If a table named in an INSERT statement contains PRIMARY KEY, UNIQUE, or NOT NULL constraints, then each row of values inserted into the table must comply with these constraints.
Examples
An example of the INSERT command is:
INSERT INTO part
VALUES ( 'P7', 'WHEEL', 'WHITE',
2, 'ATHENS' );
This statement inserts an entry for P7, a two-pound white wheel made in Athens, into the PART table. To perform this operation correctly, the data must be entered in exactly the order in which they are to appear in the table. If specified, the column list determines the insertion sequence.
The other type of INSERT involves selecting values from one
or more tables and inserting those values into the designated table. For example:
INSERT INTO part (pno, pname)
SELECT tno, tname FROM temp_parts;
In this case, each row of values in a table called temp_parts (columns tno and tname only) is inserted into the part table (columns pno and pname only). The data type for temp_parts.tno must be compatible with that of part.pno, and temp_parts.tname must be compatible with part.pname, otherwise the INSERT will fail. If there are any other columns besides pno and pname in the part table, they will contain null values for each row added by the INSERT statement.
To insert a null (unknown) value into the table explicitly, enter the keyword NULL in the appropriate column location of the INSERT command statement. Any table column can record inserted null values, unless a CREATE or ALTER TABLE command originally defined the column as NOT NULL. For example, if the weight and color of the part to be inserted are not known, type:
INSERT INTO part
VALUES ( 'P7', 'WHEEL', NULL, NULL,
'ATHENS' );
An alternative way to insert nulls is simply to leave the appropriate column location blank, since the default value for all columns is NULL (note that the same number of commas is required as in the above example):
INSERT INTO part
VALUES ( 'P7', 'WHEEL', , , 'ATHENS' );
This SQL command example is equivalent to the preceding one: nulls will be inserted into the table at the same places. Note that the same number of commas as in the previous example is required.