totn MariaDB

MariaDB: INSERT Statement

This MariaDB tutorial explains how to use the MariaDB INSERT statement with syntax and examples.

Description

The MariaDB INSERT statement is used to insert a single record or multiple records into a table in MariaDB.

Syntax

The syntax for the MariaDB INSERT statement when inserting a single record using the VALUES keyword is: 

INSERT INTO table
(column1, column2, ... )
VALUES
(expression1, expression2, ... ),
(expression1, expression2, ... ),
...;

OR

The syntax for the INSERT statement when inserting multiple records using a sub-select in MariaDB is:

INSERT INTO table
(column1, column2, ... )
SELECT expression1, expression2, ...
FROM source_table
[WHERE conditions];

Parameters or Arguments

table
The table to insert the records into.
column1, column2
The columns in the table to insert values.
expression1, expression2
The values to assign to the columns in the table. So column1 would be assigned the value of expression1, column2 would be assigned the value of expression2, and so on.
source_table
The source table when inserting data from another table.
WHERE conditions
Optional. The conditions that must be met for the records to be inserted.

Note

  • When inserting records into a table using the MariaDB INSERT statement, you must provide a value for every NOT NULL column.
  • You can omit a column from the MariaDB INSERT statement if the column allows NULL values.

Example - Using VALUES keyword

Let's look at how to use the INSERT statement in MariaDB using the VALUES keyword.

For example:

INSERT INTO sites
(site_id, site_name)
VALUES
(1, 'TechOnTheNet.com');

This MariaDB INSERT statement would result in one record being inserted into the sites table. This new record would have a site_id of 1 and a site_name of 'TechOnTheNet.com'.

You could use the syntax above to insert more than one record at a time.

For example:

INSERT INTO sites
(site_id, site_name)
VALUES
(1, 'TechOnTheNet.com'),
(2, 'CheckYourMath.com');

This INSERT example would result in two records being inserted into the sites table. The first record would have a site_id of 1 and a site_name of 'TechOnTheNet.com'. The second record would have a site_id of 2 and a site_name of 'CheckYourMath.com'.

This would be equivalent to the following two INSERT statements:

INSERT INTO sites
(site_id, site_name)
VALUES
(1, 'TechOnTheNet.com');

INSERT INTO sites
(site_id, site_name)
VALUES
(2, 'CheckYourMath.com');

Example - Using sub-select

Next, let's look at how to use the INSERT statement in MariaDB using a sub-select.

For example:

INSERT INTO contacts
(contact_id, contact_name)
SELECT site_id, site_name
FROM sites
WHERE site_name = 'TechOnTheNet.com';

By placing a SELECT statement within the INSERT statement, you can perform multiples inserts quickly.

With this type of INSERT statement, you may wish to check for the number of rows being inserted. You can determine the number of rows that will be inserted by running the following MariaDB SELECT statement before performing the insert.

SELECT COUNT(*)
FROM sites
WHERE site_name = 'TechOnTheNet.com';
Share on: