INSERT

Creates a new row in a database table.

Syntax 1:

This is the most standard syntax, working with all type of database engines.

INSERT INTO table-specification [ ( column [,...] ) ]
{
 VALUES ( { variable | sql-expression } [,...] )
|
 select-statement
}

Syntax 2:

The fglcomp compiler will automatically generate a standard INSERT statement with the complete list of members of the record. The generated SQL will depend on the definition of the record.

INSERT INTO table-specification VALUES ( record.* )

Syntax 3:

This syntax requires a database schema specification with the SCHEMA instruction, and the corresponding database schema file.

INSERT INTO table-specification VALUES record.*

where table-specification is:

[dbname[@dbserver]:][owner.]table

dbname identifies the database name.
dbserver identifies the database server (INFORMIXSERVER).
owner identifies the owner of the table, with optional double quotes.
table is the name of the database table.
column is a name of a table column.
variable is a program variable, a record member or an array member used as a parameter buffer to provide values.
sql-expression is an expression supported by the database server, this can be a literal or NULL for example.
select-statement is a static SELECT statement with or without parameters as variables.
record is the name of a record (followed by dot star in this syntax).

Usage

The INSERT SQL statement can be used to create a row in a specified database table.

The dbname, dbserver and owner prefix of the table name should be avoided for maximum SQL portability.

With INSERT INTO table VALUES ..., the statement inserts a row in the table with the values specified in variables, as literals, or with NULL. When using a record, you can specify all record members with the dot-star notation (record.*).

The third syntax can be used to avoid serial column usage in the value list. The record member corresponding to a column defined as SERIAL, SERIAL8 or BIGSERIAL in the schema file will be removed by the compiler. This is useful when using databases like Microsoft™ SQL Server, where IDENTITY columns must be omitted in INSERT statements.

When using a select-statement, the statement inserts all rows returned in the result set of the SELECT statement. The columns returned by the result set must match the column number and data types of the target table. For SQL portability, it is not recommended that you use this syntax.

Example

MAIN
   DEFINE myrec RECORD
            key INTEGER,
            name CHAR(10),
            cdate DATE,
            comment VARCHAR(50)
         END RECORD
   DATABASE stock 
   LET myrec.key     = 123
   LET myrec.name    = "Katos"
   LET myrec.cdate   = TODAY
   LET myrec.comment = "xxxxxx"
   INSERT INTO items VALUES ( 123, 'Practal', NULL, myrec.comment )
   INSERT INTO items VALUES ( myrec.* )
   INSERT INTO items VALUES myrec.*  -- without serial (if one is used)
   INSERT INTO items SELECT * FROM histitems WHERE name = myrec.name 
END MAIN