Previous Topic

Next Topic

Insert

Valid in: SQL, ESQL, DBProc

The Insert statement inserts rows into a table.

Previous Topic

Next Topic

Syntax

The Insert statement has the following format:

[EXEC SQL [REPEATED]]INSERT INTO [schema.]table_name

              [(column {, column})]

              [VALUES (expr{, expr})] | [subselect];

Previous Topic

Next Topic

Description

The Insert statement inserts new rows into the specified table. Use either the values list or specify a subselect. When using the values list, insert only a single row with each execution of the statement. If specifying a subselect, the statement inserts all the rows that result from the evaluation of the subselect. For the syntax of subselect, see Select (interactive).

The column list identifies the columns of the specified table into which the values are placed. When including the column list, the DBMS Server places the result of the first expression in the values list or subselect into the first column named, the second value into the second column named, and so on. The data types of the values must be compatible with the data types of the columns in which they are placed.

The list of column names can be omitted under the following circumstances:

A value cannot be specified to be inserted into a system_maintained table_key or object_key column (because the values for these data types are system-generated). For this reason, the column names must be specified when inserting into a table that has logical key columns.

When including the column list, any columns in the table that are not specified in the column list are assigned a default value or a null, depending on how the column was defined when the table was created. For details about column defaults, see Create Table.

Expressions in the values list can only be constants (including the null constant), scalar functions on constants, or arithmetic operations on constants.

Note: To insert long varchar or long byte columns, specify a datahandler clause in place of the host language variable in the values clause. For details about data handler routines, see the chapter "Working with Embedded SQL" and the Embedded SQL Companion Guide. The syntax for the datahandler clause is as follows:

datahandler(handler_routine ([handler_arg]))[:indicator_var]

Note: If II_DECIMAL is set to comma, be sure that when SQL syntax requires a comma (such as a list of table columns or SQL functions with several parameters), that the comma is followed by a space. For example:

select col1, ifnull(col2, 0), left(col4, 22) from t1:


© 2007 Ingres Corporation. All rights reserved.