MariaDB INSERT INTO SELECT Statement

Objectives

Understanding of MariaDB INSERT INTO SELECT Statement
MariaDB INSERT INTO SELECT all Data
MariaDB INSERT INTO SELECT Data with Condition

Create database and table and insert data if it is not created already

--- Create database if not exist
CREATE DATABASE IF NOT EXISTS mariadb_tutorial;

--- Select the database for further operation
USE mariadb_tutorial;

--- Create table if not exist
CREATE TABLE IF NOT EXISTS person (
    id int(12) NOT NULL AUTO_INCREMENT,
    first_name varchar(150) NOT NULL,
    last_name varchar(150),
    email varchar(100),
    age int,
    income double,
    PRIMARY KEY (id)
)

--- Remove old Data
TRUNCATE person;

--- Insert data into table if not exist
INSERT INTO person (first_name, last_name, email, age, income)
VALUES
	('Faiyaz', 'Mia', 'faiyaz@pf.local', 1, 5000),
	('John', 'Doe', 'john@pf.local', 19, 100),
	('Tahsin', NULL, 'tahsin@pf.local', 10, 150),
	('Jane', 'Doe', 'jane@gmail.com', 26, 300),
	('Rakib', 'Mia', 'rakib@bf.local', 24, 200),
	('Sagor', 'Sowrov', 'email10@bf.local', 20, 250),
	('Touhid', NULL, 'hmtmcse.com@gmail.com', 30, 500);

Understanding of MariaDB INSERT INTO SELECT Statement

The INSERT INTO SELECT statement copies data from one table and inserts it into another table. Let’s try to understand by practical example, assume that you have a table called person, and it has lots of data, now you get a requirement you have to create new table called customer and need to copy firstname, lastname, email from person table. In these types of situation you can use INSERT INTO SELECT.

MariaDB Create Customer table for INSERT INTO SELECT Statement

--- Create table if not exist
CREATE TABLE IF NOT EXISTS customer (
    id int(12) NOT NULL AUTO_INCREMENT,
    first_name varchar(150) NOT NULL,
    last_name varchar(150),
    email varchar(100),
    password varchar(100),
    PRIMARY KEY (id)
);

MariaDB INSERT INTO SELECT all Data

Problem: Copy firstname, lastname, email from person table to customer table

Syntax

-- Use when source table and destination table column are same name and order
INSERT INTO table2 SELECT * FROM table1;
-- OR
INSERT INTO table2 (column1, column2, column3, ...) SELECT column1, column2, column3, ... FROM table1;

Example

INSERT INTO customer (first_name, last_name, email) SELECT first_name, last_name, email FROM person;

Output

Query OK, 7 rows affected (0.115 sec)
Records: 7  Duplicates: 0  Warnings: 0

After insert data the SELECT output

SELECT * FROM customer;

+----+------------+-----------+-----------------------+----------+
| id | first_name | last_name | email                 | password |
+----+------------+-----------+-----------------------+----------+
|  1 | Faiyaz     | Mia       | faiyaz@pf.local       | NULL     |
|  2 | John       | Doe       | john@pf.local         | NULL     |
|  3 | Tahsin     | NULL      | tahsin@pf.local       | NULL     |
|  4 | Jane       | Doe       | jane@gmail.com        | NULL     |
|  5 | Rakib      | Mia       | rakib@bf.local        | NULL     |
|  6 | Sagor      | Sowrov    | email10@bf.local      | NULL     |
|  7 | Touhid     | NULL      | hmtmcse.com@gmail.com | NULL     |
+----+------------+-----------+-----------------------+----------+
7 rows in set (0.000 sec)

MariaDB INSERT INTO SELECT Data with Condition

Problem: Copy firstname, lastname, email from person table to customer table, condition only email from pf.local