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 |
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.
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 |
---|---|
|
Name of the transaction that controls the execution of the |
|
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 |
|
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.
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';