MariaDB Insert Multiple Rows Into a Table

Summary: in this tutorial, you will learn how to use the MariaDB insert statement to insert multiple rows into a table.

Introduction to MariaDB insert multiple rows statement

To insert multiple rows into a table using a single insert statement, you use the following syntax:

insert into
    table_name(column_list)
values
    (value_list_1),
    (value_list_2),
    (value_list_3),
    ...;
Code language: SQL (Structured Query Language) (sql)

In this syntax:

First, specify the name of the table and a list of columns in parentheses.
Second, specify a list of comma-separated lists of values that correspond to the columns. Each element of the list maps to a row in the table.

MariaDB insert multiple rows example

We’ll use the contacts table for the demonstration. Note that the contacts table was created in the insert statement tutorial.

create table if not exists contacts(
    id int auto_increment,
    first_name varchar(50) not null,
    last_name varchar(50) not null,
    full_name varchar(101) 
        as (concat(first_name, ' ', last_name)) virtual,
    phone varchar(100),
    contact_group varchar(50) default 'General',
    primary key(id)
);
Code language: SQL (Structured Query Language) (sql)

A) Inserting multiple rows into a table example

The following statement inserts three rows into the contacts table:

insert into contacts(first_name, last_name, phone, contact_group)
values
    ('James','Smith','(408)-232-2352','Customers'),
    ('Michael','Smith','(408)-232-6343','Customers'),
    ('Maria','Garcia','(408)-232-3434','Customers');
Code language: SQL (Structured Query Language) (sql)

MariaDB will issue the following message:

Affected rows: 3  Found rows: 0  Warnings: 0  Duration for 1 query: 0.016 sec.
Code language: SQL (Structured Query Language) (sql)

It means that the three rows have been inserted successfully.

The following query shows the contents of the contacts table:

select * from contacts;
Code language: SQL (Structured Query Language) (sql)

B) Inserting multiple rows into a table and returning inserted id example

When you insert multiple rows into a table that has an auto_increment column, the last_insert_id() function returns the first generated id, not the last id. In addition, it does not return all the inserted ids.

Let’s see the following example.

First, insert two rows into the contacts table:

insert into contacts(first_name, last_name, phone, contact_group)
values
    ('James','Johnson','(408)-232-4523','Customers'),
    ('Mary','Rodriguez','(408)-232-4532','Customers');
Code language: SQL (Structured Query Language) (sql)

Second, query the contents of the contacts table:

select * from contacts;
Code language: SQL (Structured Query Language) (sql)

Third, get the insert id using the last_insert_id() function:

select last_insert_id();
Code language: SQL (Structured Query Language) (sql)

The returned id is 7, not 8.

In this tutorial, you learned how to use the MariaDB insert statement to insert multiple rows into a table.

Was this tutorial helpful ?