INSERT
This statement inserts new rows into a table.
Synopsis
- InsertIntoStmt
- TableOptimizerHints
- PriorityOpt
- IgnoreOptional
- IntoOpt
- TableName
- PartitionNameListOpt
- InsertValues
- OnDuplicateKeyUpdate
InsertIntoStmt ::=
'INSERT' TableOptimizerHints PriorityOpt IgnoreOptional IntoOpt TableName PartitionNameListOpt InsertValues OnDuplicateKeyUpdate
TableOptimizerHints ::=
hintComment?
PriorityOpt ::=
( 'LOW_PRIORITY' | 'HIGH_PRIORITY' | 'DELAYED' )?
IgnoreOptional ::=
'IGNORE'?
IntoOpt ::= 'INTO'?
TableName ::=
Identifier ( '.' Identifier )?
PartitionNameListOpt ::=
( 'PARTITION' '(' Identifier ( ',' Identifier )* ')' )?
InsertValues ::=
'(' ( ColumnNameListOpt ')' ( ValueSym ValuesList | SelectStmt | '(' SelectStmt ')' | UnionStmt ) | SelectStmt ')' )
| ValueSym ValuesList
| SelectStmt
| UnionStmt
| 'SET' ColumnSetValue? ( ',' ColumnSetValue )*
OnDuplicateKeyUpdate ::=
( 'ON' 'DUPLICATE' 'KEY' 'UPDATE' AssignmentList )?
Examples
mysql> CREATE TABLE t1 (a INT);
Query OK, 0 rows affected (0.11 sec)
mysql> CREATE TABLE t2 LIKE t1;
Query OK, 0 rows affected (0.11 sec)
mysql> INSERT INTO t1 VALUES (1);
Query OK, 1 row affected (0.02 sec)
mysql> INSERT INTO t1 (a) VALUES (1);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO t2 SELECT * FROM t1;
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM t1;
+------+
| a |
+------+
| 1 |
| 1 |
+------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM t2;
+------+
| a |
+------+
| 1 |
| 1 |
+------+
2 rows in set (0.00 sec)
mysql> INSERT INTO t2 VALUES (2),(3),(4);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM t2;
+------+
| a |
+------+
| 1 |
| 1 |
| 2 |
| 3 |
| 4 |
+------+
5 rows in set (0.00 sec)
MySQL compatibility
This statement is understood to be fully compatible with MySQL. Any compatibility differences should be reported via an issue on GitHub.
See also
Was this page helpful?