MariaDB Insert Into Select

Summary: in this tutorial, you will learn how to use the MariaDB insert into select statement to insert result sets of a query into a table.

The value list of an insert statement can be either literal values or the result set of a query.

The following shows the syntax of the insert statement whose value list comes from a select statement:

insert into table_name(column_list)
select select_list
from table_name
...;
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the table and a list of columns that you want to insert data.
• Second, specify the select statement that returns a result set whose columns are corresponding to the column_list;

The insert into select statement can be very useful to copy data from one table to another table or to insert a summary data from tables into a table.

MariaDB insert into select examples

We’ll use the tables countries and regions from the sample database for the demonstration:

A) Inserting rows from a table into another table example

First, create a new table called small_countries:

create table small_countries(
    country_id int primary key,
    name varchar(50) not null,
    area decimal(10,2) not null,
);
Code language: SQL (Structured Query Language) (sql)

Second, insert countries whose areas are less than 50,000 km2 from the countries table into the small_countries table:

insert into small_countries
    (country_id,name,area)
select 
    country_id, name, area 
from 
    countries
where 
    area < 50000;
Code language: SQL (Structured Query Language) (sql)

Third, query data from the small_countries table:

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

B) Inserting summary data of tables into another table example

First, create a table called region_areas that stores names and areas of regions.

create table region_areas(
    region_name varchar(100) not null,
    region_area decimal(15,2) not null,
    primary key(region_name)
);
Code language: SQL (Structured Query Language) (sql)

Second, query data from tables countries and regions and insert it into the table region_areas:

insert into region_areas
    (region_name, region_area)
select 
    regions.name, 
    sum(area)
from 
    countries
inner join regions 
    using (region_id)
group by   
    regions.name;
Code language: SQL (Structured Query Language) (sql)

Third, query data from the region_areas table:

select * 
from 
    region_areas 
order by 
    region_area;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the MariaDB insert into select statement to insert a result set of a query into a table.