FirebirdSQL logo
 CommentsProcedural SQL (PSQL) Statements 

INSERT …​ VALUES

The VALUES list must provide a value for every column in the column list, in the same order and of the correct type.The column list need not specify every column in the target but, if the column list is absent, the engine requires a value for every column in the table or view (computed columns excluded).

The expression DEFAULT allows a column to be specified in the column list, but instructs Firebird to use the default value (either NULL or the value specified in the DEFAULT clause of the column definition).For identity columns, specifying DEFAULT will generate the identity value.It is possible to include calculated columns in the column list and specifying DEFAULT as the column value.

Note

Introducer syntax provides a way to identify the character set of a value that is a string constant (literal).Introducer syntax works only with literal strings: it cannot be applied to string variables, parameters, column references or values that are expressions.
Examples
INSERT INTO cars (make, model, year)
VALUES ('Ford', 'T', 1908);

INSERT INTO cars
VALUES ('Ford', 'T', 1908, 'USA', 850);

-- notice the '_' prefix (introducer syntax)
INSERT INTO People
VALUES (_ISO8859_1 'Hans-Jörg Schäfer');

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.