SQL Reference

Chapter 2 | Supported standards 18
Example
Insert a list of expressions.
INSERT INTO emp (last_name, first_name, emp_id, salary, hire_date)
VALUES ('Smith', 'John', 'E22345', 27500, DATE '2019-06-05')
Each INSERT
statement adds one record to the database table. In this case a record has been added
to the employee database table, emp. Values are specified for five columns. The remaining columns in
the table are assigned a blank value, meaning Null.
Note 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:
INSERT INTO table_name (container_name) VALUES(? AS 'filename.file extension')
Unsupported file types will be inserted as type FILE.
When using the PutA
s() 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” on page 16.
The SELECT st
atement 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.
Example
Insert using a SELECT 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 comp
atible. 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 st
atement is evaluated before any values are inserted.