SQL INSERT

Purpose

Inserts one or more rows into a table

Syntax

INSERT INTO [<database>!]<table> [(<column> [,...])]

VALUES(<expr> [, ] | NULL [, ] | <empty> [, ] [,...]) | <sub-query> | [FROM] XML <.xml file>

INSERT INTO [<database>!]<table>

FROM ARRAY <array> | FROM MEMVAR | FROM NAME <ObjectName> | FROM OBJECT <ObjectName>

See Also

CREATE DATABASE, CREATE TABLE, EXECUTE IMMEDIATE, GETENV(), OPEN DATABASE, PSEUDO COLUMNS, SELECT, SET XMLFORMAT, UPDATE

Description

The INSERT command inserts one or more rows into a table. An INSERT statement with a VALUES clause adds a single row to the table. An INSERT statement with a sub-query adds the rows returned by the query. To insert data you must be the owner of the table or have already been granted INSERT privileges.

Keywords	Description
database	The name of the database to which the table belongs. Databases in Recital are implemented as directories containing files that correspond to the tables and associated files in the database. Operating System file protection can be applied individually to the files for added security. The directory is a sub-directory of the Recital data directory. The environment variable DB_DATADIR points to the current Recital data directory and can be queried using the GETENV() function. Files from other directories can be added to the database using the ADD TABLE command or via the database catalog and SET AUTOCATALOG functionality. The '!' character must be included between the database name and the table name.
table	The name of the table into which to insert the rows. When data is being inserted into encrypted tables, the <table> can include the three-part encryption key, enclosed in angled brackets, appended to the table name. The SET ENCRYPTION command allows a default encryption key to be defined. If the key is not included in the <table>, this default key will be used. If the default key is not the correct key for the table, an error will be given. If no default key is active, a dialog box will be displayed in Recital Terminal Developer to allow the user to enter the key.
column	The column name from the table. In the inserted row each column in this list is assigned a value from the VALUES clause or the sub-query. If you omit the column list altogether, then you must supply values for each column in the table.
VALUES	Specifies the row of values to be inserted into the table. Each <expr> must be the same data type as the column it will update. If a column has a default value defined in the Applications Data Dictionary (.dbd), the value can be omitted and the default value will be inserted. Date constants can be specified as valid dates in the current format (SET DATE, SET CENTURY, SET MARK) or as a character string in the format "DD-MMM-YYYY", e.g. "01-Sep-2002".
sub-query	The sub-query is a SELECT statement that returns rows that are to be inserted into the table.
[FROM] XML <.xml file>	The values to be inserted into the table are taken from the specified XML file. The XML file must be in ADO (Microsoft® ActiveX® Data Objects) format.
FROM ARRAY <array>	The values to be inserted into the table are taken from an existing array, whose name is specified in <array>.
FROM MEMVAR	The values to be inserted into the table are taken from existing memory variables with the same names as the columns. If the corresponding memory variable does not exist, the column is left blank.
FROM NAME <ObjectName>	The values to be inserted into the table are taken from an object whose properties have the same names as the columns. If the corresponding property does not exist, the column is left blank.
FROM OBJECT <ObjectName>	Same as FROM NAME <ObjectName>.

Example

// Add a new row and update the column values.
INSERT INTO accounts!balances;
  (acc_prefix, acc_no, balance);
  VALUES ('hmt', 'a12345', m_value*1.75)
 
// Encrypted table example.
INSERT INTO encbal<key_1,key_2,key_3>;
  (acc_prefix, acc_no, balance);
  VALUES ('hmt', 'a12345', m_value*1.75)

Products

Recital Server, Recital