INSERT

RETURNING clause
UNION allowed in feeding SELECT

Available in: DSQL, ESQL, PSQL

Changed in: 2.0

Description: Adds rows to a database table, or to one or more tables underlying a view. Field values can be given in the VALUES clause (in which case exactly one row is inserted) or they can come from a SELECT statement.

Syntax:

INSERT [TRANSACTION name]
   INTO {tablename | viewname} [(<columns>)]
   {VALUES (<values>) [RETURNING <values> [INTO <variables>]]
    | <select_expr>}

<columns>      ::=  colname  [, colname  ...]
<values>       ::=  value    [, value    ...]
<variables>    ::=  :varname [, :varname ...]
<select_expr>  ::=  a SELECT returning a set whose columns fit the target
Restrictions

The TRANSACTION directive is only available in ESQL.

The RETURNING clause is not available in ESQL.

The “INTO <variables>” subclause is only available in PSQL.

The trigger context variables OLD and NEW must not be preceded by a colon (“:”).

New in 2.0: No column may appear more than once in the insert list.

RETURNING clause

Available in: DSQL, PSQL

Added in: 2.0

Description: An “INSERT ... VALUES” query may optionally specify a RETURNING clause in order to return the values that have actually been stored. The clause, if present, need not contain all of the insert columns and may also contain other columns or expressions. The returned values reflect any changes that may have been made in BEFORE tiggers, but not those in AFTER triggers.

Example:

insert into Scholars (firstname, lastname, address, phone, email)
  values ('Henry', 'Higgins', '27A Wimpole Street', '3231212', null)
  returning lastname, fullname, id

Note: In Firebird 2.0, the RETURNING clause is only supported for “INSERT ... VALUES” queries. With “INSERT ... SELECT” it is rejected, even if it concerns a singleton select. This limitation will be lifted in version 2.1.

UNION allowed in feeding SELECT

Changed in: 2.0

Description: A SELECT query used in an INSERT statement may now be a UNION.

Example:

insert into Members (number, name)
  select number, name from NewMembers where Accepted = 1
    union
  select number, name from SuspendedMembers where Vindicated = 1