Outdated Version

You are viewing an older version of this section. View current production version.

INSERT

Insert 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)

Notes

  • MemSQL supports constants, DEFAULT, or nullary builtins such as NOW(), RAND(), or UNIX_TIMESTAMP() for expressions (expr) for INSERTs.
  • Arithmetic expressions are not currently supported in the VALUES clause.
  • 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.
  • This command must be run on the master aggregator or a child aggregator node (see Node Requirements for MemSQL Commands).

Example

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