FirebirdSQL logo
 CommentsProcedural SQL (PSQL) Statements 

INSERT …​ SELECT

For this method of inserting, the output columns of the SELECT statement (or <query-expression>) must provide a value for every target column in the column list, in the same order and of the correct type.

Literal values, context variables or expressions of compatible type can be substituted for any column in the source row.In this case, a source column list and a corresponding VALUES list are required.

If the column list is absent — as it is when SELECT * is used for the source expression — the column_list must contain the names of every column in the target table or view (computed columns excluded).

Examples
INSERT INTO cars (make, model, year)
  SELECT make, model, year
  FROM new_cars;

INSERT INTO cars
  SELECT * FROM new_cars;

INSERT INTO Members (number, name)
  SELECT number, name FROM NewMembers
    WHERE Accepted = 1
UNION ALL
  SELECT number, name FROM SuspendedMembers
    WHERE Vindicated = 1

INSERT INTO numbers(num)
  WITH RECURSIVE r(n) as (
    SELECT 1 FROM rdb$database
    UNION ALL
    SELECT n+1 FROM r WHERE n < 100
  )
SELECT n FROM r

Of course, the column names in the source table need not be the same as those in the target table.Any type of SELECT statement is permitted, as long as its output columns exactly match the insert columns in number, order and type.Types need not be the same, but they must be assignment-compatible.

Note

Since Firebird 5.0, an INSERT …​ SELECT with a RETURNING clause produces zero or more rows, and the statement is described as type isc_info_sql_stmt_select.In other words, an INSERT …​ SELECT …​ RETURNING will no longer produce a “multiple rows in singleton select” error when the select produces multiple rows.

For the time being, a INSERT …​ VALUES (…​) or INSERT …​ DEFAULT VALUES with a RETURNING clause is still described as isc_info_sql_stmt_exec_procedure.This behaviour may change in a future Firebird version.

INSERT …​ DEFAULT VALUES

The DEFAULT VALUES clause allows insertion of a record without providing any values at all, either directly or from a SELECT statement.This is only possible if every NOT NULL or CHECKed column in the table either has a valid default declared or gets such a value from a BEFORE INSERT trigger.Furthermore, triggers providing required field values must not depend on the presence of input values.

Specifying DEFAULT VALUES is equivalent to specifying a values list with expression DEFAULT for all columns.

Example
INSERT INTO journal
  DEFAULT VALUES
RETURNING entry_id;