> ## Documentation Index
> Fetch the complete documentation index at: https://docs.regatta.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# INSERT

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](/sql/appendices/indentifiers-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](/sql/ddl-commands/create-table) or [ALTER TABLE](/sql/ddl-commands/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](/sql/dql/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](/sql/dql/select/select#parameters) 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](/sql/ddl-commands/create-table/create-table#examples) Examples.

Insert of a single row:

```sql theme={null}
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:

```sql theme={null}
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:

```sql theme={null}
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.