Join the SingleStore Community Today
Get expert advice, develop skills, and connect with others.
Inserts data into a table.
INSERT [IGNORE] [INTO] tbl_name [(col_name,...)] [VALUES | VALUE] (expr,...),(...),... [ ON DUPLICATE KEY UPDATE col_name = insert_expr [, col_name = insert_expr] ... ] INSERT [IGNORE] [INTO] tbl_name [(col_name,...)] SELECT ... [ ON DUPLICATE KEY UPDATE col_name = insert_expr [, col_name = insert_expr] ... ] INSERT [IGNORE] [INTO] tbl_name SET col_name=expr, ... [ ON DUPLICATE KEY UPDATE col_name = insert_expr [, col_name = insert_expr] ... ] insert_expr: expr | VALUES(col_name)
TIMESTAMP(6)has special behavior for insert operations, defaulting to the current timestamp value. Refer to the discussion of these types in the Data Types topic for more details. In addition, a field
fdeclared with the
DEFAULT <value>modifier will be set to
<value>if no explicit value for
DEFAULT, or nullary builtins such as
UNIX_TIMESTAMP()for expressions (
expr) for INSERTs.
INSERTqueries will fail if the maximum_table_memory limit has been reached. See /admin/memory_limits_include for more information.
INSERT ... SELECTqueries cannot be run inside transactions. See What isolation levels does MemSQL provide.
INSERT IGNOREdisables transactions for multi-INSERT. In this case, MemSQL will ignore records with duplicate keys and, without rolling back, continue inserting records with unique keys. This can speed up performance of multi-INSERT because it avoids an extra roundtrip between the aggregators and leaves.
IGNOREmodifier, 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.
ON DUPLICATE KEY UPDATEclause is specified, and a row is to be inserted that would result in a duplicate value in a
UNIQUEindex, MemSQL will instead perform an
UPDATEof the old row.
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.
Here’s a simple insert:
INSERT INTO mytbl (v) VALUES ("hello"), ("goodbye");
This example shows a successful insert even when converting a
NULL value to
int NOT NULL type, with the
NULL replaced by
memsql> CREATE TABLE mytbl2(a int not null); Query OK, 0 rows affected (0.72 sec) memsql> INSERT IGNORE mytbl2 VALUES(null); Query OK, 1 row affected (0.18 sec) memsql> SELECT * FROM mytbl2; +---+ | a | +---+ | 0 | +---+ 1 row in set (0.24 sec)