# INSERT

The `INSERT` command inserts data into a table.

## Syntax

```
INSERT [IGNORE] [INTO] <table_name> [(<column_name>,...)]
    [VALUES | VALUE] (expr,...),(...),...
    [ ON DUPLICATE KEY UPDATE
      <column_name> = insert_expr
        [, <column_name> = insert_expr] ... ]
    [ ON DUPLICATE KEY DELETE WHEN insert_expr ELSE UPDATE insert_expr ]

INSERT [IGNORE] [INTO] <table_name> [(col_name,...)]
    SELECT [WITH(force_random_reshuffle=1)] ...
    [ ON DUPLICATE KEY UPDATE
      <column_name> = insert_expr
        [, <column_name> = insert_expr] ... ]
    [ ON DUPLICATE KEY DELETE WHEN insert_expr ELSE UPDATE insert_expr ]

INSERT [IGNORE] [INTO] <table_name>
    SET <column_name>=expr, ...
    [ ON DUPLICATE KEY UPDATE
      <column_name> = insert_expr
        [, <column_name>= insert_expr] ... ]
    [ ON DUPLICATE KEY DELETE WHEN insert_expr ELSE UPDATE insert_expr ]

 insert_expr:
      expr
    | VALUES(<column_name>)

```

## Remarks

* A field of type `TIMESTAMP` or `TIMESTAMP(6)` declared with the `DEFAULT <value>` modifier will be set to `<value>` if no explicit value is specified. Refer to the discussion of these types in the [Data Types](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-types.md) topic for more details.
* SingleStore supports constants, `DEFAULT`, or nullary builtins such as `NOW()`, `RAND()`, or `UNIX_TIMESTAMP()` for expressions (`expr`) for INSERTs.
* `INSERT` queries will fail if the `maximum_table_memory` limit has been reached.

  Refer to [Addressing the “Maximum Table Memory” Error](https://docs.singlestore.com/db/v9.1/user-and-cluster-administration/maintain-your-cluster/managing-memory/addressing-the-maximum-table-memory-error.md) for more information.
* `INSERT IGNORE` discards rows with duplicate keys for both singleton and multi-row inserts. In this case, SingleStore discards records with duplicate keys and, without rolling back, continues inserting records with unique keys. This can speed up performance of multi-INSERT because it avoids an extra roundtrip between the aggregators and leaves.
* Multi-inserts are atomic in nature. For a multi-insert transaction, either all the rows are committed or the transaction is rolled back.
* Multi-inserts may or may not have consecutive `AUTO_INCREMENT` values. See [AUTO\_INCREMENT behavior](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-definition-language-ddl/create-table.md) for more information.
* When you use the `IGNORE` modifier, data conversion errors for a row being inserted are ignored, and the row will be inserted with default values for fields where the conversion failed.
* If the `ON DUPLICATE KEY UPDATE` clause is specified, and a row is to be inserted that would result in a duplicate value in a `PRIMARY KEY` or `UNIQUE` index, SingleStore will instead perform an `UPDATE` of the old row.
* The `IGNORE` modifier and `ON DUPLICATE KEY UPDATE` clause cannot be used in the same `INSERT` query, because their semantics contradict each other. For more information, see [Query Errors](https://docs.singlestore.com/db/v9.1/reference/troubleshooting-reference/query-errors/error-1706-hy-000-leaf-error-127-0-0-1-3307-feature-insert-ignore-on-duplicate-key-update-is-not-supported-by-memsql.md).
* When using `ON DUPLICATE KEY UPDATE`, the affected row count will be 0 if no existing row is changed, 1 if a new row is inserted, and 2 if an existing row is updated.
* `ON DUPLICATE KEY UPDATE` cannot update unique key columns in columnstore tables. For rowstore tables, `ON DUPLICATE KEY UPDATE` is allowed as long as the updated column isn't part of the table's explicitly defined shard key or primary key if there's no explicitly defined shard key.
* This command must be run on the master aggregator or a child aggregator node (see [Cluster Management Commands](https://docs.singlestore.com/db/v9.1/reference/sql-reference/cluster-management-commands.md)).
* `INSERT ... SELECT` into `AUTO_INCREMENT` columns can only be pushed down to leaves if the source column is also `AUTO_INCREMENT`, because `AUTO_INCREMENT` values are generated on the aggregator. This prevents insertion of `0` or `NULL` values in the `AUTO_INCREMENT` column, if the source column isn’t `AUTO_INCREMENT`. For more information, see [AUTO\_INCREMENT Behavior](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-definition-language-ddl/create-table.md).
* SingleStore supports `INSERT ... SELECT` across databases. The column data types that are being inserted must match the column data types from selected table:
  ```sql
  INSERT INTO db2.test2.t2 (col1, col2, ...) SELECT col1, col2,... FROM db1.test1.t1;
  ```
* Transactions that span more than one database are not supported. An attempt to write to multiple databases within a single transaction fails with the following error: `Feature 'Cross-database transactions is not supported by SingleStore.`
* Scalar expression are not supported as part of the VALUES clause:
  ```
   INSERT INTO t1 (col1, col2) VALUES ((SELECT col1 FROM t2 LIMIT 1), 1);
  ```
* `INSERT … ON DUPLICATE KEY [UPDATE|IGNORE]` has a variation which instead of the optional update or ignore if there is a duplicate key, it can DELETE the duplicate.
* Refer to the [Permissions Matrix](https://docs.singlestore.com/db/v9.1/reference/sql-reference/security-management-commands/permissions-matrix.md) for the required permissions.

## force\_random\_reshuffle

To reduce skew, you can force the `INSERT … SELECT` to redistribute the data with the `force_random_reshuffle` query hint. A common use case for this hint is on keylessly sharded tables, as it allows forced redistribution of data among partitions, rather than inserting it locally into the same partition.

## On Duplicate Key Update

`ON DUPLICATE KEY UPDATE` allows you to perform an upsert of a row. For more information, see the [Performing Upserts](https://docs.singlestore.com/db/v9.1/query-data/advanced-query-topics/performing-upserts.md) topic.

## Examples

## Simple Insert

```sql
INSERT INTO mytbl (v) VALUES ("hello"), ("goodbye");

```

This example shows a successful insert even when converting a `NULL` value to an `int NOT NULL` type, with the `NULL` replaced by `0`:

```sql
CREATE TABLE mytbl2(a int not null);

INSERT IGNORE mytbl2 VALUES(null);

SELECT * FROM mytbl2;

```

```output

+---+
| a |
+---+
| 0 |
+---+
1 row in set (0.24 sec)

```

## ON DUPLICATE KEY UPDATE and force\_random\_reshuffle

This example shows a simple `INSERT` with both `ON DUPLICATE KEY UPDATE` and `force_random_reshuffle`. The rows from `mytbl_new` will be inserted into `mytbl`, and distributed across partitions.

```sql
INSERT INTO mytbl (column1, column2, column3)
    SELECT WITH(force_random_reshuffle=1)
    * FROM mytbl_new ON DUPLICATE KEY UPDATE
      column1 = VALUES(column1),
      column2 = VALUES(column2),
      column3 = VALUES(column3);

```

```output

Query OK, 29 rows affected (0.18 sec)

```

## ON DUPLICATE KEY DELETE

This example shows how to use delete on a duplicate key.

Create a table :

```sql
CREATE TABLE viewing_stats (program_id INT PRIMARY KEY NOT NULL, view_count INT);
```

Create a stored procedure that will delete duplicate keys.

```sql
DELIMITER //
CREATE OR REPLACE PROCEDURE view_tally(_program_id INT, _view_count INT) AS
BEGIN
INSERT INTO viewing_stats VALUES(_program_id, _view_count)
ON DUPLICATE KEY DELETE
  WHEN view_count + values(view_count) <= 0
ELSE UPDATE view_count = view_count + _view_count;
END//
DELIMITER ;
```

Use CALL statements to enter data into the table:

```sql
CALL view_tally(1,1);
CALL view_tally(2,1);
CALL view_tally(3,1);
CALL view_tally(4,1);
CALL view_tally(5,1);
```

Verify the records were loaded:

```sql
SELECT * FROM viewing_stats;


```

```output

+------------+------------+
| program_id | view_count |
+------------+------------+
|          5 |           1|
|          4 |           1|
|          3 |           1|
|          2 |           1|
|          1 |           1|
+------------+------------+

```

Use a CALL statement to add a duplicate:

```sql
CALL view_tally(2,1);
```

Verify the view count has increased for program\_id number 2:

```sql
SELECT * FROM viewing_stats;


```

```output

+------------+------------+
| program_id | view_count |
+------------+------------+
|          5 |           1|
|          4 |           1|
|          3 |           1|
|          2 |           2|
|          1 |           1|
+------------+------------+
```

Use a CALL statement to delete a record:

```sql
CALL view_tally(1,-1);
```

Verify that program\_id 1 has been removed from the table:

```sql
SELECT * FROM viewing_stats;


```

```output

+------------+------------+
| program_id | view_count |
+------------+------------+
|          5 |           1|
|          4 |           1|
|          3 |           1|
|          2 |           2|
+------------+------------+
```

## Related Topics

* [LOAD DATA](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/load-data.md)
* [REPLACE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/replace.md)

***

Modified at: June 12, 2026

Source: [/db/v9.1/reference/sql-reference/data-manipulation-language-dml/insert/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/insert/)

(An index of the documentation is available at /llms.txt)