Insert

The Insert statement is used to add new rows to a table. You can specify either of the following options:

■	List of values to be inserted as a new row

■	Select statement that copies data from another table to be inserted as a set of new rows

Grammar

INSERT INTO table_name [(column_name[,column_name]...)]

{VALUES (expression [,expression]...) | select_statement}

table_name is the name of the table in which you want to insert rows.

column_name is optional and specifies an existing column. Multiple column names (a column list) must be separated by commas. A column list provides the name and order of the columns, the values of which are specified in the Values clause. If you omit a column_name or a column list, the value expressions must provide values for all columns defined in the table and must be in the same order that the columns are defined for the table. Table columns that do not appear in the column list are populated with the default value, or with NULL if no default value is specified. See “Specifying an External ID Column” for more information.

expression is the list of expressions that provides the values for the columns of the new record. Typically, the expressions are constant values for the columns. Character string values must be enclosed in single quotation marks (’). See “Literals” for more information.

select_statement is a query that returns values for each column_name value specified in the column list. Using a Select statement instead of a list of value expressions lets you select a set of rows from one table and insert it into another table using a single Insert statement. The Select statement is evaluated before any values are inserted. This query cannot be made on the table into which values are inserted. See “Select” for information about Select statements.

Specifying an External ID Column

To specify an external ID column to look up the value of a foreign key column, use the following syntax:

column_name EXT_ID [schema_name.[table_name.] ]ext_id_column

where:

EXT_ID is used to specify that the column specified by ext_id_column is used to look up the rowid to be inserted into the column specified by column_name.

schema_name is the name of the schema of the table that contains the foreign key column being specified as the external ID column.

table_name is the name of the table that contains the foreign key column being specified as the external ID column.

ext_id_column is the external ID column.

Examples

Example A

The following example uses a list of expressions to insert records. Each Insert statement adds one record to the database table. In this case, one record is added to the table emp. Values are specified for five columns. The remaining columns in the table are assigned the default value or NULL if no default value is specified.

INSERT INTO emp (last_name,

VALUES ('Smith', 'John', 'E22345', 27500, {1999-04-06})

Example B

The following example uses a Select statement to insert records. The number of columns in the result of the Select statement must match exactly the number of columns in the table if no column list is specified, or it must match the number of column names specified in the column list. A new entry is created in the table for every row of the Select result.

INSERT INTO emp1 (first_name,

SELECT first_name, last_name, emp_id, dept, salary FROM emp

WHERE dept = 'D050'

Example C

The following example uses a list of expressions to insert records and specifies an external ID column (a foreign key column) named accountId that references a table that has an external ID column named AccountNum.

INSERT INTO emp (last_name,

accountId EXT_ID AccountNum)

VALUES ('Smith', 'John', 'E22345', 27500, {1999-04-06}, 0001)