INSERT

From Lianjapedia
Jump to: navigation, search

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. Multiple VALUES clauses (comma-separated) can be specified to add more than one row. 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. 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.
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 2 new rows and update the column values.
INSERT INTO accounts!balances;
  (acc_prefix, acc_no, balance);
  VALUES ('hmt', 'a12345', m_value*1.75),;
  VALUES ('hmt', 'a12346', 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)