21. 4GL Statement Glossary : Insert
 
Share this page                  
Insert
Inserts rows into a database table.
Syntax
[repeated] insert into [owner.]tablename [(columnname 
  {, columnname})]  values (expression
   
 {, expression}) | subselect 
[owner.]tablename
Specifies the name of the database table into which you are inserting rows or records, and the owner of the table. The table name can be a 4GL name; however, if a 4GL variable is used, an owner cannot be specified, unless the owner name is included as part of the value. In other words, the variable can contain the value owner.tablename.
columnname
Specifies the name of a column within the database table into which you are inserting values. A 4GL name.
expression
Specifies an expression representing a value to be inserted (as the corresponding columnname).
An expression used in the values clause must usually be a constant, a field, a table-field column, or a database expression. expression can also be built from a function or an arithmetic operation using constants or single-valued fields. In addition, it can be built from a global or local variable, a literal, a record or an array record attribute.
subselect
Specifies a select statement that serves as the source of values to be inserted. For complete information see the SQL Reference Guide.
Description
The 4GL insert statement lets you insert rows into a database table, using values from the current form. If you plan to use the same insert statement frequently, use the repeated option.
The values can originate from simple fields, table field columns, variables, record or array attributes. To insert multiple rows of values from a table field into a table, place the insert statement inside an unloadtable loop. See the section Unloadtable for further information.
The 4GL insert statement is similar to the Interactive SQL insert. It requires either a values clause or a subselect statement. You can omit the list of database columns if the inserted values come from a subselect and if the column names in the subselect match the column names in the table. Otherwise, you can use a values clause to supply values.
When inserting values from a table field, the current row is assumed. To specify a different row, include the row number in brackets immediately following the table-field name.
The reserved word all, attached to the name of the current form or a table field in the current form, can substitute for the list of field names in the values clause. You can use all this way if each of the simple fields in the form or each of the columns in the table field corresponds in name and data type to a column in the table. An asterisk (*) is substituted for the usual list of database table columns. It is not necessary for each database table column to be mapped to a form object. Local variables and hidden table-field columns are ignored in the mapping.
Examples
Insert values of projname and enddate into the Projects table and then commit the changes:
insert into projects (name, duedate)
  values (projname, enddate);
 commit;
Insert all values in simple fields of Deptform into the Personnel table:
insert into personnel (*)
  values (deptform.all);
Insert all values in the third row of partstbl into part and then commit the changes:
insert into part (*)
  values (partstbl[3].all);
 commit;
Insert values in Projform into the table named in the Tablename field (fields and columns correspond):
insert into :tablename (*)
  values (projform.all);
Insert a computed value into the Personnel table:
insert into personnel (name, sal)
  values (name, salary*1.1);