INSERT
On this page
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] ... ]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] ... ]INSERT [IGNORE] [INTO] <table_name>SET <column_name>=expr, ...[ ON DUPLICATE KEY UPDATE<column_name> = insert_expr[, <column_name>= insert_expr] ... ]insert_expr:expr| VALUES(<column_name>)
Remarks
-
A field of type
TIMESTAMP
orTIMESTAMP(6)
declared with theDEFAULT <value>
modifier will be set to<value>
if no explicit value is specified.Refer to the discussion of these types in the Data Types topic for more details. -
SingleStore supports constants,
DEFAULT
, or nullary builtins such asNOW()
,RAND()
, orUNIX_
for expressions (TIMESTAMP() expr
) for INSERTs. -
INSERT
queries will fail if themaximum_
limit has been reached.table_ memory -
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_
values.INCREMENT See AUTO_ INCREMENT behavior 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 aPRIMARY KEY
orUNIQUE
index, SingleStoreDB Cloud will instead perform anUPDATE
of the old row. -
The
IGNORE
modifier andON DUPLICATE KEY UPDATE
clause cannot be used in the sameINSERT
query, because their semantics contradict each other.For more information, see Query Errors. -
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. -
INSERT .
into. . SELECT AUTO_
columns can only be pushed down to leaves if the source column is alsoINCREMENT AUTO_
, becauseINCREMENT AUTO_
values are generated on the aggregator.INCREMENT This prevents insertion of 0
orNULL
values in theAUTO_
column, if the source column isn’tINCREMENT AUTO_
.INCREMENT For more information, see AUTO_ INCREMENT Behavior. -
SingleStoreDB supports
INSERT .
across databases.. . SELECT The column datatypes that are being inserted must match the column datatypes from selected table: INSERT INTO db2.test2.t2 (col1, col2, ...) SELECT col1, col2,... FROM db1.test1.t1; -
Writing to multiple databases in a transaction is not supported.
-
Scalar expression are not supported as part of the VALUES clause:
INSERT INTO t1 (col1, col2) VALUES ((SELECT col1 FROM t2 LIMIT 1), 1); -
Refer to the Permission Matrix for the required permission.
force_ random_ reshuffle
To reduce skew, you can force the INSERT … SELECT
to redistribute the data with the force_
query hint.
ON DUPLICATE KEY UPDATE
ON DUPLICATE KEY UPDATE
allows you to perform an upsert of a row.
Examples
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
:
CREATE TABLE mytbl2(a int not null);INSERT IGNORE mytbl2 VALUES(null);SELECT * FROM mytbl2;
+---+
| 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_
.mytbl_
will be inserted into mytbl
, and distributed across partitions.
INSERT INTO mytbl (column1, column2, column3)SELECT WITH(force_random_reshuffle=1)* FROM mytbl_new ON DUPLICATE KEY UPDATEcolumn1 = VALUES(column1),column2 = VALUES(column2),column3 = VALUES(column3);
Query OK, 29 rows affected (0.18 sec)
Related Topics
Last modified: July 28, 2023