Inserts data into an existing table.

​
Synopsis

INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
  { VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
  [ WITH devices = ( device_list ) IN MODULES ( module_list ) ]
  [ RETURNING output_expression [[ AS ] output_name ] [, ...] ]

where device_list is a comma-separated list of device numbers and module_list is a comma-separated list of the module identifiers of the modules to which the devices belong.
Note that the device number is the number of the device within the pertinent module, not the system-wide unique device-id.

​
Description

The INSERT command inserts one or more rows into an existing table. The values inserted into each column in the table can either be explicitly-specified using the VALUES keyword, or use the result of a query.

​
Parameters

table_name The name of the table to be inserted with the new rows. alias A substitute alias name for table_name. The alias name should match the Identifiers Limitations. column_name A list of columns in the table. The columns can be listed in any order, as long as the expressions in the VALUES clause are listed in the same order. Each missing column is set to NULL or to its default value specified in the CREATE TABLE statement. In case of a missing column that has no default value and the column has a NOT NULL constraint, the command fails. This parameter is optional. If no column names were specified, the values are assigned to the columns in the order of columns defined in the table’s creation. If the number of values is less than the number of columns, the values are assigned to the first columns and the rest are assigned default values, or NULL for columns with no default value. VALUES Specifies the values to insert into the corresponding columns in the table. The data types of the values should be compatible with the data types of the corresponding columns in the column list (note that in some cases, Regatta will overcome mismatches with implicit conversion). The list of values can only contain literals (e.g. ‘hello’), the DEFAULT and NULL keywords, expressions or functions that can be computed at the planning time such as NOW() or 5*50. String literals and date literals in the VALUES list must be enclosed by single quotes (’). expression An expression or value to assign to the corresponding column. The expression is given as a string and Regatta attempts to convert it to the desired data type. DEFAULT Store the column default value as it’s defined for the column at the CREATE TABLE or ALTER TABLE statements. Note that if all the columns in the table have DEFAULT values, the following syntax can be used to insert a single row with all the columns set to the default values: INSERT INTO <table name> DEFAULT VALUES; query A SELECT statement that supplies the rows to be inserted. See SELECT. WITH devices = ( device_list ) IN MODULES ( module_list ) Regatta uses an internal algorithm to distribute the rows of a table across the table storage devices optimally. You can override it by specifying one or more device into which the rows will be inserted. The devices are identified by their internal device number within the pertinent modules, and modules containing the devices should also be specified. Use of this clause is generally discouraged as it could interfere with Regatta’s internal data distribution algorithms and degrade performance and/or cause a storage device to fill up unintentionally. It should be used with care in very specific use cases. RETURNING output_expression Returns the given expression for every row inserted by the command. Refer to the expression parameter of SELECT for specification of the output_expression parameter. If only ROWID is specified as the output_expression, the returned ROWIDs are returned in pairs of (First ROWID, number of rows) for each set of consecutive ROWID numbers.

​
Outputs

On successful completion, the INSERT command returns the count of the inserted rows.

​
Examples

The commands to create the tables referenced in these examples can be found in the CREATE TABLE Examples. Insert of a single row: 
INSERT INTO customers (c_custkey,c_name,c_gender)
VALUES (1,'John Doe1','M');
Insert of multiple rows in a single command: INSERT INTO customers (c_custkey,c_name,c_gender)
VALUES (2, ‘John Doe2’, ‘M’), (3, ‘John Doe3’, ‘M’), (4, ‘John Doe4’, ‘M’); Insert partial column list: 
INSERT INTO orders (o_order_id,o_name,o_custkey,o_date)
VALUES (1022, 'order of John Doe1', 1, DEFAULT);
In this example, we specifically write the values of the mentioned columns. The column o_date is loaded with its DEFAULT value as defined in the table definition. Missing columns (o_totalprice) will write their default value, or NULL. If no DEFAULT was defined and the column is set with the NOT NULL constraint, the INSERT will fail. Insert based on a query result: 
INSERT INTO old_orders SELECT * FROM orders WHERE o_date < '2020-01-01';

​
Compatibility

The INSERT command conforms to the SQL standard with the exception of the RETURNING functionality.