Outdated Version
You are viewing an older version of this section. View current production version.
INSERT
Inserts data into a table.
Syntax
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)
Remarks
- The first field of type
TIMESTAMP
orTIMESTAMP(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 fieldf
declared with theDEFAULT <value>
modifier will be set to<value>
if no explicit value forf
is set. - MemSQL supports constants,
DEFAULT
, or nullary builtins such asNOW()
,RAND()
, orUNIX_TIMESTAMP()
for expressions (expr
) for INSERTs. INSERT
queries will fail if the maximum_table_memory limit has been reached. See /admin/memory_limits_include for more information.INSERT ... SELECT
queries cannot be run inside transactions. See What isolation levels does MemSQL provide.INSERT IGNORE
disables 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.- 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 aPRIMARY KEY
orUNIQUE
index, MemSQL will instead perform anUPDATE
of the old row. - 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. - This command must be run on the master aggregator or a child aggregator node (see Node Requirements for MemSQL Commands). Note that when running this command on reference tables you must connect to the master aggregator.
- Writing to multiple databases in a transaction is not supported.
Examples
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
an int NOT NULL
type, with the NULL
replaced by 0
:
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)