INSERT
This page is part of MariaDB's Documentation.
The parent of this page is: SQL Statements for MariaDB Xpand
Topics on this page:
Overview
Adds a row or rows of data to table.
USAGE
Common Syntax:
INSERT [IGNORE]
[INTO] <tbl_name>
[(<col_name> [, <col_name>] ...)]
{ {VALUES | VALUE} (<value_list>) [, (<value_list>)] ... }
[ON DUPLICATE KEY UPDATE
SET <col_name> = {<value> | DEFAULT} [, <col_name> = {<value> | DEFAULT}] ...]
INSERT [IGNORE]
[INTO] <tbl_name>
SET <col_name> = {<value> | DEFAULT} [, <col_name> = {<value> | DEFAULT}] ...
[ON DUPLICATE KEY UPDATE
SET <col_name> = {<value> | DEFAULT} [, <col_name> = {<value> | DEFAULT}] ...]
INSERT [IGNORE]
[INTO] <tbl_name>
[(<col_name> [, <col_name>] ...)]
{ SELECT ... }
[ON DUPLICATE KEY UPDATE
SET <col_name> = {<value> | DEFAULT} [, <col_name> = {<value> | DEFAULT}] ...]
EXAMPLES
INSERT
To insert 1 row of data into a table:
INSERT INTO customers (customer_id, customer_name, customer_email, customer_status)
VALUES (1, 'East Corp', 'east@example.com', 'active');
Omit column names if the order of the supplied values follows the table definition:
INSERT INTO customers VALUES (2, 'East LLC', 'east@example.net', 'inactive');
The order of values does not matter when using the SET clause:
INSERT INTO customers
SET customer_name = 'East NGO', customer_email = 'east@example.org',
customer_status = 'active', customer_id = 3;
Here are the added rows:
SELECT * FROM customers WHERE customer_id IN (1, 2, 3);
+-------------+---------------+------------------+-----------------+
| customer_id | customer_name | customer_email | customer_status |
+-------------+---------------+------------------+-----------------+
| 1 | East Corp | east@example.com | active |
| 2 | East LLC | east@example.net | inactive |
| 3 | East NGO | east@example.org | active |
+-------------+---------------+------------------+-----------------+
To insert multiple rows of data following the order of columns in the table definition:
INSERT INTO customers VALUES
(4, 'West Corp', 'west@example.com', 'suspended'),
(5, 'West LLC', 'west@example.net', 'inactive'),
(6, 'West NGO', 'west@example.org', 'active');
INSERT .. SELECT
To insert data selected from another table:
CREATE TABLE suspended_customers LIKE customers;
INSERT INTO suspended_customers
SELECT * FROM customers WHERE customer_status = 'suspended';
Here is the contents of the suspended_customers table:
SELECT * FROM suspended_customers;
+-------------+---------------+------------------+-----------------+
| customer_id | customer_name | customer_email | customer_status |
+-------------+---------------+------------------+-----------------+
| 4 | West Corp | west@example.com | suspended |
+-------------+---------------+------------------+-----------------+
INSERT into Tables with AUTO_UNIQUE Columns
To use an automatically generated AUTO_UNIQUE value while inserting data into a table, omit the AUTO_UNIQUE column name and its value in the INSERT statement:
INSERT INTO db1.invoices
(branch_id, customer_id, invoice_date, invoice_currency,
invoice_total, payment_method, invoice_status)
VALUES (1, 1, '2022-08-01 14:22:12.579346', 'EUR', 1049.78, 'card', 'paid');
Here is the inserted row:
SELECT * FROM invoices WHERE invoice_date = '2022-08-01 14:22:12.579346';
+---------------------+-----------+-------------+----------------------------+------------------+---------------+----------------+----------------+
| invoice_id | branch_id | customer_id | invoice_date | invoice_currency | invoice_total | payment_method | invoice_status |
+---------------------+-----------+-------------+----------------------------+------------------+---------------+----------------+----------------+
| 7145237195634249729 | 1 | 1 | 2022-08-01 14:22:12.579346 | EUR | 1049.78 | card | paid |
+---------------------+-----------+-------------+----------------------------+------------------+---------------+----------------+----------------+
AUTO_UNIQUE is a better performing alternative to AUTO_INCREMENT in environments with high levels of concurrency and parallelism.
For additional information on inserting data into tables with AUTO_UNIQUE columns, see "AUTO_UNIQUE Constraints with MariaDB Xpand".
INSERT into Tables with AUTO_INCREMENT Columns
To use an automatically generated AUTO_INCREMENT value while inserting data into a table, omit the AUTO_INCREMENT column name and its value in the INSERT statement:
INSERT INTO customers (customer_name, customer_email, customer_status)
VALUES ('North Corp', 'north@example.com', 'active');
To find the inserted row by the latest auto-incremented id:
SELECT * FROM customers WHERE customer_name = 'North Corp';
+-------------+---------------+-------------------+-----------------+
| customer_id | customer_name | customer_email | customer_status |
+-------------+---------------+-------------------+-----------------+
| 7 | North Corp | north@example.com | active |
+-------------+---------------+-------------------+-----------------+
AUTO_UNIQUE is a better performing alternative to AUTO_INCREMENT in environments with high levels of concurrency and parallelism.
For additional information on inserting data into tables with AUTO_INCREMENT columns, see "AUTO_INCREMENT Constraints with MariaDB Xpand".
INSERT .. ON DUPLICATE KEY UPDATE
An "upsert" is a single operation that performs an "update" if a matching row exists and otherwise performs an "insert". With MariaDB Xpand, INSERT .. ON DUPLICATE KEY UPDATE can be used to perform upsert operations. Alternatively, the REPLACE SQL statement can be used, which performs a DELETE and INSERT if a matching row exists, and otherwise performs an INSERT.
For example, when INSERT .. ON DUPLICATE KEY UPDATE is executed and a duplicate unique key or primary key value is found, MariaDB Xpand updates the respective row with the list of values provided after the ON DUPLICATE KEY UPDATE clause:
INSERT INTO customers (customer_id, customer_name, customer_email, customer_status)
VALUES (7, 'North Inc', 'north@example.com', 'active')
ON DUPLICATE KEY UPDATE
customer_name = 'North Inc', customer_email = 'north@example.com', customer_status = 'active';
Here is the updated row:
SELECT * FROM customers WHERE customer_id = 7;
+-------------+---------------+-------------------+-----------------+
| customer_id | customer_name | customer_email | customer_status |
+-------------+---------------+-------------------+-----------------+
| 7 | North Inc | north@example.com | active |
+-------------+---------------+-------------------+-----------------+
Ignore Errors
Normally, when an INSERT statement encounters an error, the statement is rolled back, and an error is raised to the client. With INSERT IGNORE, certain types of errors are ignored, and the rows that do not cause errors are inserted.
To ignore certain types of errors, use the INSERT IGNORE statement:
INSERT IGNORE INTO customers (customer_id, customer_name, customer_email, customer_status)
VALUES (7, 'South Inc', 'south@example.com', 'active'),
(8, 'North LLC', 'north@example.net', 'active');
In the example above, a row already exists with customer_id=7, so the first row would normally raise a duplicate key error. However, with INSERT IGNORE, the error is ignored. A SELECT query shows that the table still contains the original row with customer_id=7, but it also contains the newly inserted row with customer_id=8:
SELECT * FROM customers
WHERE customer_id IN (7, 8);
+-------------+---------------+-------------------+-----------------+
| customer_id | customer_name | customer_email | customer_status |
+-------------+---------------+-------------------+-----------------+
| 8 | North LLC | north@example.net | active |
| 7 | North Inc | north@example.com | active |
+-------------+---------------+-------------------+-----------------+