MariaDB Insert Multiple Rows [Complete Guide]

In this MariaDB tutorial, we will discuss using the MariaDB Insert Multiple Rows statements and look at several examples. There are lists of the topic that comes under discussion:

• MariaDB Insert Multiple Rows
• MariaDB Insert Multiple Rows On Duplicate Key Update
• MariaDB Insert Multiple Rows From Table
• MariaDB Insert Multiple Rows Group By
• MariaDB Insert Multiple Rows Json
• MariaDB Insert Multiple Rows Like
• MariaDB Insert Multiple Rows With Condition

Also, check the latest MariaDB tutorial: MariaDB If statement in Select

MariaDB Insert Multiple Rows

We’ll look at how to use the MariaDB INSERT statement to insert several rows into a table, using syntax and an example.

Let’s see the syntax of the INSERT MULTIPLE ROWS statement by the following query:

SYNTAX:

INSERT INTO YOUR_TABLE_NAME(COLUMN_NAME_1,COLUMN_NAME_2,COLUMN_NAME_N)
VALUES(VALUE_1,VALUES_2,VALUES_N);

The syntax explanation:

• We must first define the table’s name and a set of columns in brackets.
• Second, we must provide a set of semicolon values for each column. A table row corresponds to each item on the list.

For this example, we’ll use the EMPLOYEE_DETAILS table. The INSERT INTO statement method was used to construct the EMPLOYEE_DETAILS table.

CREATE TABLE EMPLOYEE_DATA (
	EMP_ID INT,
	EMP_FIRST_NAME VARCHAR(60),
	EMP_LAST_NAME VARCHAR(60),
	FULL_NAME VARCHAR(150)
	AS (CONCAT(EMP_FIRST_NAME,' ',EMP_LAST_NAME)) VIRTUAL,
	EMAIL VARCHAR(50));
	
INSERT INTO EMPLOYEE_DATA (FIRST_NAME,LAST_NAME,EMAIL) VALUES 
('Thatch', 'Linfoot', 'tlinfoot0@smh.com.au'),
('Alfy', 'Squirrel', 'asquirrel1@trellian.com'),
('Kimball', 'Hesse', 'khesse2@canalblog.com'),
('Benedikta', 'Dressel', 'bdressel3@kickstarter.com'),
('Cacilie', 'Bulfield', 'cbulfield4@wufoo.com'),
('Mendie', 'Karpol', 'mkarpol5@meetup.com'),
('Alonso', 'Tuohy', 'atuohy6@princeton.edu'),
('Roy', 'Esp', 'resp7@amazon.com'),
('Garwood', 'Gemeau', 'ggemeau8@mlb.com'),
('Jennette', 'Giacoppoli', 'jgiacoppoli9@51.la');

SELECT * FROM EMPLOYEE_DATA;	

In the first query, we created a table called EMPLOYEE_DATA with new columns as EMP_ID, EMP_FIRST_NAME, EMP_LAST_NAME, and EMAIL in it. And it’s created by the CREATE TABLE statement.

In the second query, we have inserted 10 new records into the EMPLOYEE_DATA table by using the INSERT INTO statement. If we want to retrieve new records from the EMPLOYEE_DATA table, then we will use the SELECT statement.

Read: MariaDB Insert Into Select

MariaDB Insert Multiple Rows On Duplicate Key Update

When inserting a row into a table, we can use the MariaDB INSERT ON DUPLICATE KEY UPDATE statement to update if a duplicate in the UNIQUE index or a PRIMARY KEY issue occurs.

In MariaDB, the INSERT ON DUPLICATE KEY UPDATE statement is an extension of the MariaDB INSERT statement. So, when we insert new records into the table and the rows cause a duplicate in a UNIQUE index or PRIMARY KEY, MariaDB will throw an error.

Let’s have a look at the INSERT ON DUPLICATE KEY UPDATE statement by the following query:

SYNTAX:

INSERT INTO YOUR_TABLE_NAME(COLUMN_NAME_LIST)
VALUES(VALUE_LIST)
ON DUPLICATE KEY UPDATE
COLUMN_NAME=VALUE_1,
COLUMN_NAME_2=VALUE_2;

The ON DUPLICATE KEY UPDATE clause, which provides a list of column-value-pair assigns in case of a duplicate, is the only change to the INSERT statement.

The phrase basically tries to add a new row into the table first. It will update the current row with the value supplied in the ON DUPLICATE KEY UPDATE clause if a duplicate error occurs.

MariaDB calculates the number of rows affected by the action it takes:

• The number of targeted rows is one if a new row is added.
• The number of rows affected is two if an existing row is updated.
• The number of rows affected is 0 if the existing row is modified with its current values.

First, we have created a table named EMPLOYEE_DATA to store employee details in it.

CREATE TABLE EMPLOYEE_DATA (
	EMP_ID INT,
	FIRST_NAME VARCHAR(50),
	LAST_NAME VARCHAR(50),
	FULL_NAME VARCHAR(150)
	AS (CONCAT(FIRST_NAME,' ',LAST_NAME)) VIRTUAL,
	EMAIL VARCHAR(50));

Next, we have inserted some rows into the EMPLOYEE_DATA table by the following query:

INSERT INTO EMPLOYEE_DATA (FIRST_NAME,LAST_NAME,EMAIL) VALUES 
('Thatch', 'Linfoot', 'tlinfoot0@smh.com.au'),
('Alfy', 'Squirrel', 'asquirrel1@trellian.com'),
('Kimball', 'Hesse', 'khesse2@canalblog.com'),
('Benedikta', 'Dressel', 'bdressel3@kickstarter.com'),
('Cacilie', 'Bulfield', 'cbulfield4@wufoo.com'),
('Mendie', 'Karpol', 'mkarpol5@meetup.com'),
('Alonso', 'Tuohy', 'atuohy6@princeton.edu'),
('Roy', 'Esp', 'resp7@amazon.com'),
('Garwood', 'Gemeau', 'ggemeau8@mlb.com'),
('Jennette', 'Giacoppoli', 'jgiacoppoli9@51.la');

Then, we will query the data from the EMPLOYEE_DATA table to confirm the insert:

SELECT FIRST_NAME,LAST_NAME,EMAIL FROM EMPLOYEE_DATA;

As we have inserted 10 rows into the EMPLOYEE_DATA table. Again we want to insert one more record into the EMPLOYEE_DATA table.

INSERT INTO EMPLOYEE_DATA (FIRST_NAME,LAST_NAME,EMAIL)
VALUES ('Katine', 'Course', 'kcoursea@goo.ne.jp')
ON DUPLICATE KEY UPDATE 
FIRST_NAME='Katine',
LAST_NAME='Course',
EMAIL='kcoursea@goo.ne.jp';

Because there is no duplicate, MariaDB will insert a new row into the EMPLOYEE_DATA table. The above statement has the same effect as the following statement:

insert into EMPLOYEE_DATA(FIRST_NAME, LAST_NAME, EMAIL) values ('Katine', 'Course', 'kcoursea@goo.ne.jp');

Finally, insert a new row with a duplicate value in the EMP_ID column:

INSERT INTO EMPLOYEE_DATA(FIRST_NAME,LAST_NAME,EMAIL)
VALUES('Katine', 'Course', 'kcoursea@goo.ne.jp')
ON DUPLICATE KEY UPDATE 
FIRST_NAME='JOHN'
LAST_NAME='WICK',
EMAIL='wick.john001@underworld.com';

In this query, we have inserted duplicate rows with the FIRST_NAME as JOHN, LAST_NAME as WICK, and EMAIL as a wick.john001@underworld.com for the EMPLOYEE_DATA table. The INSERT ON DUPLICATE KEY UPDATE statement has been used to duplicate rows in the EMPLOYEE_DATA table.

Read: MariaDB Insert If Not Exists

MariaDB Insert Multiple Rows Into Table

Here we will use the MariaDB INSERT statement to insert multiple rows into a table and it’s explained with the help of an illustrated example.

In MariaDB, the INSERT statement is used to insert new records with different data types into the table. As we have already table called EMPLOYEE_DATA which is shown below:

CREATE TABLE EMPLOYEE_DATA (
	EMP_ID INT,
	FIRST_NAME VARCHAR(50),
	LAST_NAME VARCHAR(50),
	FULL_NAME VARCHAR(150)
	AS (CONCAT(FIRST_NAME,' ',LAST_NAME)) VIRTUAL,
	EMAIL VARCHAR(50));

Let’s see an example of the INSERT statement for multiple rows in a table by the following query:

EXAMPLE:

INSERT INTO EMPLOYEE_DATA (EMP_ID, FIRST_NAME, LAST_NAME, EMAIL) VALUES 
('Katuscha', 'Rehme', 'krehmeb@zdnet.com'),
('Jessie', 'Craigie', 'jcraigiec@dailymail.co.uk'),
('Janean', 'Scarf', 'jscarfd@ifeng.com'),
('Madge', 'Jump', 'mjumpe@adobe.com');

SELECT * FROM EMPLOYEE_DATA;

In the first query, we added new records of 4 rows into the EMPLOYEE_DATA table by using the INSERT INTO statement. If we want to retrieve whether the last 4 records have been inserted or not in the EMPLOYEE_DATA table then we will use the SELECT statement.

Read: MariaDB Greatest Function

MariaDB Insert Multiple Rows Group By

Here we will use the INSERT INTO SELECT statement to insert multiple rows with the GROUP BY clause in MariaDB and which is explained with the help of an illustrated example.

An INSERT statement’s value list can be either explicit values or the query outcome. The MariaDB INSERT INTO SELECT statement is useful for copying data from one table to another or inserting summarized data from multiple tables into one table.

First, we will have a look at the EMPLOYEE_DATA table by the following query:

SELECT * FROM EMPLOYEE_DATA;

The MariaDB SELECT statement is used to retrieve all records from the EMPLOYEE_DATA table.

Now we will create another table called the EMPLOYEE_LIFE table which will insert new records from the EMPLOYEE_DATA table. Here is an example below:

CREATE TABLE EMPLOYEE_LIFE (
	EMP_ID INT,
	FIRST_NAME VARCHAR(50),
	LAST_NAME VARCHAR(50),
	FULL_NAME VARCHAR(150)
	AS (CONCAT(FIRST_NAME,' ',LAST_NAME)) VIRTUAL,
	EMAIL VARCHAR(50));

Here is an example of the INSERT INTO SELECT statement to insert multiple records into the EMPLOYEE_LIFE table as given below:

EXAMPLE:

INSERT INTO EMPLOYEE_LIFE(EMP_ID,FIRST_NAME,LAST_NAME,FULL_NAME,EMAIL)
SELECT EMP_ID,FIRST_NAME,LAST_NAME,EMAIL FROM EMPLOYEE_DAT
GROUP BY FIRST_NAME;

SELECT * FROM EMPLOYEE_LIFE;

As we see in the above query, we have inserted new records into the EMPLOYEE_LIFE table by using the INSERT INTO statement. In the SELECT statement, we have selected all records of all the columns from the EMPLOYEE_DATA table and grouped them on the basis of the FIRST_NAME column by using the GROUP BY clause.

If we want to check whether new records have been inserted or not into the EMPLOYEE_LIFE table, for that we will use the SELECT statement to retrieve it in the result set.

Read: MariaDB Between + Examples

MariaDB Insert Multiple Rows JSON

Here we will use the MariaDB INSERT INTO SELECT statement with the JSON data type and which is explained with the help of an illustrated example.

 MariaDB JSON data type is a replacement for LONGTEXT, which was designed to work with MySQL’s JSON data type. Because it violates the SQL standard, MariaDB supports the JSON data format as a LONGTEXT instead, and MariaDB’s benchmarks show that efficiency is at least comparable.

Here is an illustrated example of the MariaDB INSERT INTO SELECT statement with the JSON data type in the below following query:

EXAMPLE:

CREATE TABLE JSON_DATAS(
USER_NAME VARCHAR(30),
JSON_VALUE LONGTEXT);

INSERT INTO JSON_DATAS(USER_NAME,JSON_VALUE)
SELECT USER_NAME,JSON_VALUE 
FROM JSON_DEMO;

SELECT * FROM JSON_DATAS;

In the first query, we have created another table called JSON_DATAS which will insert all records from the JSON_DEMO table. And it is used by the CREATE TABLE statement.

In the second query, the columns like USER_NAME and JSON_VALUE of the JSON_DATAS table are used by the INSERT INTO statement.

And it selected all the records of the USER_NAME and JSON_VALUE columns of the JSON_DEMO table from which all records will be transferred to the JSON_DATAS table by using the SELECT statement.

If we want to check that all records have been retrieved from the JSON_DATAS table then we will use the SELECT statement.

Read: MariaDB Not Between

MariaDB Insert Multiple Rows Like

Here we will use the MariaDB INSERT INTO SELECT statement with the LIKE clause in the query and which is explained with the help of an illustrated example.

The LIKE operator is a logical operator in MariaDB that returns TRUE when any string matches the specific pattern provided with the LIKE operator. In other words, with the help of a like operator, we can test whether the string or expression matches the pattern.

If the string or expression matches with the pattern, then it returns TRUE otherwise FALSE. The pattern is used with the LIKE operator, and this pattern is called wildcards. Two wildcards are used with the like operator in MariaDB.

• Percent(%): It is called a percent wildcard that matches any string with any number of characters.
• Underscore(_): It is called an underscore wildcard that matches any single character.

Here is an illustrated example of the MariaDB INSERT INTO SELECT statement with the LIKE clause by the following query:

EXAMPLE:

CREATE TABLE APPLE_EMPLOYEES(
EMP_ID INT, 
first_name VARCHAR(20),
LAST_NAME VARCHAR(30));

INSERT INTO APPLE_EMPLOYEES(EMP_ID,first_name,LAST_NAME)
SELECT EMP_ID,first_name,LAST_NAME FROM employee 
WHERE first_name LIKE 'A%';

SELECT * FROM APPLE_EMPLOYEES;

• In the first query, we have created another table called APPLE_EMPLOYEES which will insert records from the EMPLOYEE table. And it was created by using the CREATE TABLE statement.
• In the second query, by using the INSERT INTO statement for the APPLE_EMPLOYEES table for insertion of data.
• And it will select the records of all the columns of the EMPLOYEE table by using the WHERE condition.
• In the WHERE condition, it will collect the FIRST_NAME column whose name starts with the alphabet A.
• If the WHERE condition gets TRUE, it will retrieve records from the EMPLOYEE table to the APPLE_EMPLOYEES table.
• We can use the SELECT statement to retrieve new records of the APPLE_EMPLOYEE table based on the LIKE condition.

Read: MariaDB GROUP BY with Example

MariaDB Insert Multiple Rows With Condition

Here we will use the INSERT INTO SELECT statement to insert multiple rows with the WHERE condition in a query. And which is explained with the help of an illustrated example.

In MariaDB, the WHERE condition is used to retrieve the selected part based on the condition by using the SELECT statement. First, we need to create another table called COMPANIES_EMPLOYEES which will insert data from the EMPLOYEE table. Here’s an illustrated example of the MariaDB INSERT INTO SELECT statement with the WHERE condition in the below query:

EXAMPLE:

CREATE TABLE COMPANIES_EMPLOYEES(
EMP_ID INT, 
first_name VARCHAR(20),
LAST_NAME VARCHAR(30));

INSERT INTO COMPANIES_EMPLOYEES(EMP_ID,first_name,LAST_NAME)
SELECT EMP_ID,first_name,LAST_NAME FROM employee 
WHERE EMP_ID<=10;

SELECT * FROM COMPANIES_EMPLOYEES;

In the first query, we have created another table called COMPANIES_EMPLOYEES which will insert records from the EMPLOYEE table. And it carries the EMP_ID, FIRST_NAME, and LAST_NAME columns with INT and VARCHAR data types. The CREATE TABLE statement has been used for creating the COMPANIES_EMPPLOYEES table.

In the second query, by using the INSERT INTO statement to insert new records for all the columns of the COMPANIES_EMPLOYEES table. In the SELECT statement, we have used the WHERE condition to retrieve records till 10 for all columns from the EMPLOYEE table. And transfer it to the COMPANIES_EMPLOYEES table,

If you want to check whether new records have been inserted or not then we will use the SELECT statement for the COMPANIES_EMPLOYEES table.

Also, take a look at some more MariaDB tutorials.

• MariaDB Rename Table
• MariaDB Date_Format
• MariaDB Select Unique
• MariaDB JSON Function
• MariaDB vs Postgres
• MariaDB Check Constraint
• Delete a Database in MariaDB
• MariaDB Check If Rows Exists

So, in this MariaDB tutorial, we have discussed how to use the MariaDB Insert Multiple Rows statement and discussed several examples related to it. There are lists of the topic that comes under discussion:

• MariaDB Insert Multiple Rows
• MariaDB Insert Multiple Rows On Duplicate Key Update
• MariaDB Insert Multiple Rows From Table
• MariaDB Insert Multiple Rows Group By
• MariaDB Insert Multiple Rows Json
• MariaDB Insert Multiple Rows Like
• MariaDB Insert Multiple Rows With Condition