INSERT

Go Up to Statement and Function Reference (Language Reference Guide)

Adds one or more new rows to a specified table. Available in gpre, DSQL, and isql.

INSERT [TRANSACTION transaction] INTO object [(<col> [, <col> …])]
{VALUES (val [, val …]) | select_expr};

object = tablename | viewname
val = {:variable | constant | expr
| function | udf ([val [, val …]])
| NULL | USER | RDB$DB_KEY | ?
} [COLLATE collation]
constant = num | 'string' | charsetname 'string'
function = CAST (val AS data_type)
| UPPER (val)
| GEN_ID (generator, val)

Argument	Description
<expr>	A valid SQL expression that results in a single column value
<select_expr>	A `SELECT` that returns zero or more rows and where the number of columns in each row is the same as the number of items to be inserted

Notes on the INSERT statement:

In SQL and isql, you cannot use val as a parameter placeholder (like “?”).
In DSQL and isql, val cannot be a variable.
You cannot specify a COLLATE clause for Blob columns.

Important:
In SQL statements passed to DSQL, omit the terminating semicolon. In embedded applications written in C and C++, and in isql, the semicolon is a terminating symbol for the statement, so it must be included.

Argument	Description
`TRANSACTION` <transaction>	Name of the transaction that controls the execution of the `INSERT`
`INTO` <object>	Name of an existing table or view into which to insert data
<col>	Name of an existing column in a table or view into which to insert values
`VALUES` (<val> [, <val> …])	Lists values to insert into the table or view; values must be listed in the same order as the target columns
<select_expr>	Query that returns row values to insert into target columns

Description: INSERT stores one or more new rows of data in an existing table or view. INSERT is one of the database privileges controlled by the GRANT and REVOKE statements.

Values are inserted into a row in column order unless an optional list of target columns is provided. If the target list of columns is a subset of available columns, default or NULL values are automatically stored in all unlisted columns.

If the optional list of target columns is omitted, the VALUES clause must provide values to insert into all columns in the table.

To insert a single row of data, the VALUES clause should include a specific list of values to insert.

To insert multiple rows of data, specify a select_expr that retrieves existing data from another table to insert into this one. The selected columns must correspond to the columns listed for insert.

Important:
It is legal to select from the same table into which insertions are made, but this practice is not advised because it may result in infinite row insertions.

The TRANSACTION clause can be used in multiple transaction SQL applications to specify which transaction controls the INSERT operation. The TRANSACTION clause is not available in DSQL or isql.

Examples: The following statement, from an embedded SQL application, adds a row to a table, assigning values from host-language variables to two columns:

EXEC SQL
INSERT INTO EMPLOYEE_PROJECT (EMP_NO, PROJ_ID)
VALUES (:emp_no, :proj_id);

The next isql statement specifies values to insert into a table with a SELECT statement:

INSERT INTO PROJECTS
SELECT * FROM NEW_PROJECTS
WHERE NEW_PROJECTS.START_DATE > '6-JUN-1994';

Advance To:

INSERT CURSOR (BLOB)

INSERT

See Also

Advance To: