INSERT statement

Use the INSERT statement to create records in a database table. You can specify either:

• One or more lists of values to be inserted as new records

• A SELECT statement that copies data from another table to be inserted as a set of new records

The formats of the INSERT statement are:

INSERT INTO table_name [(column_name, ...)] VALUES [(expr, ...), ...]
INSERT INTO table_name [(column_name, ...)] SELECT column_name, ... FROM table_name [WHERE expr]

With VALUES

column_name is an optional list of column names that provides the name and order of the columns whose values are specified in the VALUES clause. If you omit column_name, the list of expressions (expr) must provide values for all columns defined in the table and must be in the same order that the columns are defined for the table.

The list of expressions (expr) gives the values for the columns of a new record. To add multiple records, specify a list of expressions in parentheses for each record, separated by commas. Usually a list of expressions consists of a constant value for each column (but the list can also be a subquery). You must enclose character string values in pairs of single quotation marks ('). To include a single quotation mark in a character string value enclosed by single quotation marks, use two single quotation marks together (for example, 'Don''t').

Subqueries must be enclosed in parentheses.

INSERT INTO emp (last_name, first_name, emp_id, salary, hire_date)
   VALUES ('Smith', 'John', 'E22345', 27500, DATE '2028-06-05'), 
   ('Marks', 'Alea', 'E24682', 28500, DATE '2029-09-30')

With SELECT

The SELECT statement is a query that returns values for each column_name value specified in the column name list. Using a SELECT statement instead of a list of value expressions lets you select a set of rows from one table and insert it into another table using a single INSERT statement.

INSERT INTO emp1 (first_name, last_name, emp_id, dept, salary)
   SELECT first_name, last_name, emp_id, dept, salary FROM emp
   WHERE dept = 'D050'

In this type of INSERT statement, the number of columns to be inserted must match the number of columns in the SELECT statement. The list of columns to be inserted must correspond to the columns in the SELECT statement just as it would to a list of value expressions in the other type of INSERT statement. For example, the first column inserted corresponds to the first column selected; the second inserted to the second, and so on.

The size and data type of these corresponding columns must be compatible. Each column in the SELECT list should have a data type that the ODBC or JDBC client driver accepts on a regular INSERT/UPDATE of the corresponding column in the INSERT list. Values are truncated when the size of the value in the SELECT list column is greater than the size of the corresponding INSERT list column.

The SELECT statement is evaluated before any values are inserted.

Notes 

• In container fields, you can INSERT text only, unless you prepare a parameterized statement and stream the data from your application. To use binary data, you may simply assign the filename by enclosing it in single quotation marks or use the PutAs() function. When specifying the filename, the file type is deduced from the file extension:

  Copy

  INSERT INTO table_name (container_name) VALUES (? AS 'filename.file extension')

  Unsupported file types will be inserted as type FILE.

  When using the PutAs() function, specify the type: PutAs(col, 'type'), where the type value is a supported file type as described in Retrieving the contents of a container field: CAST() function and GetAs() function.

• column_name may also specify a field repetition—for example, lastDates[4].