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 or 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 f declared with the DEFAULT <value> modifier will be set to <value> if no explicit value for f is set.
• MemSQL 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. 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.
• 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, MemSQL will instead perform an UPDATE 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).
• Writing to multiple databases in a transaction is not supported.

Example

memsql> INSERT INTO mytbl (v) VALUES ("hello"), ("goodbye");

Was this Article Helpful?

Thanks For Your Feedback!