Commands and Statements

Command INSERT - SQL Foundation

Inserts one or more records into a table.

Syntax
INSERT INTO <cTableName> [(<cFieldName> [, <cFieldName>, ...])]
       | VALUES (<Expression> [, <Expression>, ...])
       | SELECT <SubSelectExpression>
       | FROM MEMVAR  
       | FROM ARRAY <aExpression>
       | FROM OBJECT|NAME <oExpression> 
       [VIA (<coSession>)]
Parameters
<cTableName>
Specifies the name of the table to which the new records are added. <cTableName> can be set to the alias name of a table which is open in any workarea, or the name of a table in any database the application has a connection to.
[, <cFieldName>, ...])]
A comma-separated list with the names of the fields values are to be inserted into. If no field names are specified, INSERT initializes the fields in the order the fields appear in the table.
VALUES
DEFAULT VALUES specifies that the records are inserted using the default data defined in the dictionary or database for the corresponding field(s). In the case of free tables empty values are inserted instead. Nullable fields are not touched by the INSERT command.
(<Expression> [, <Expression>] )
A comma-separated list with the field values which are inserted into the new record. The values must be specified in the order defined in the field list.
<SubSelectExpression> )
Instead of a VALUES clause, an SQL SELECT statement can be used in an INSERT command. In this case, a new record is inserted into the table for each row in the result set of the SQL SELECT statement. If a field list is specified using the <cFieldName> parameter, the number of columns in the result set of the SQL SELECT must be the same as the number of fields specified in the field list. If no field list is specified, the number of columns in the result set must match the number of columns in the table being updated. Any SQL SELECT statement, including compound SELECTs and SELECT statements with ORDER BY and/or LIMIT clauses may be used in the <SubSelectExpression>parameter.
MEMVAR
Specifies that the contents of the memory variables (PRIVATEs/PUBLICs) with the same name as the fields specified in the field list are inserted. If no memory variable exists with the same name as a field in the list, the corresponding field remains empty.
ARRAY <aExpression>
Specifies that data from the array specified in <aExpression>is inserted into the new record. The fields of the new record are filled in the same order as they appear in the field list. The content of the first array element is inserted into the first field, the content of the second element goes into the second field and so forth.
OBJECT|NAME <oExpression>
Specifies an object whose member variables are used to fill the fields of the record being inserted. Each field specified in the field is assigned the value of the member variable with the same name as the field. Only EXPORTED member variables are considered. If a member variable does not exist for a certain field, that field remains empty.
VIA (<oSession>)
<oSession> optionally specifies the Session context for the operation. <oSession> is an object of DacSession() or any derived class and must be specified as an object expression in parentheses. If <oSession> is not specified, the SQL SELECT statement is executed by the local SQL engine. If a VIA clause is specified in an SQL statement on the other hand, the statement executes in the context defined by the session which in most cases involves a remote backend.
Description

INSERT inserts new records into a table. The commands inserts as many records as specified in the VALUES clause, or via an SQL SELECT statement. Note that in the latter case, no record may be inserted if the corresponding result set is empty. If the FROM clause is used, one record is inserted.

The target column names can be listed in any order. If no list of column names is given at all, the default is all the columns of the table in their declared order; or the first N column names, if there are only N columns supplied by the VALUES clause or query. The values supplied by the VALUES clause or query are associated with the explicit or implicit column list left-to-right.

Each column not present in the explicit or implicit column list will be filled with a default value, either its declared default value or null if there is none.

If the expression for any column is not of the correct data type, automatic type conversion will be attempted.

Examples
Simple insert

// The example demonstrates the sequence of commands necessary 
// to create and display a GUI browser. The browser is displayed 
// modal. Therefore, an event loop is not necessary. 

USE states 

INSERT INTO states(region,id,name,country) VALUES('south','HE', 'Hessen','DE') 

Feedback

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.