/    /  MySQL –  Insert Into Select

MySQL –  Insert Into Select

 

The data from one table may need to be inserted into another table within the same database or in a different database. Manually entering this data using the INSERT query is not very convenient. By using the MySQL INSERT INTO SELECT query, we can optimize this process. By using this method, we are able to populate the MySQL tables quickly. The purpose of this section is to provide an overview of the INSERT INTO SELECT command, its syntax, and its usage.

We can insert values into a table using the INSERT INTO SELECT statement in MySQL. This query copies data from one table and inserts it into another table. Before using this statement, it is important to consider the following:

  • Source and target tables must have the same data types.
  • Existing records in the target table should not be affected.

When we need to copy data from one table to another table or summarize data from multiple tables into one table, the INSERT INTO SELECT command is an excellent choice.

Syntax

INSERT INTO table_name (column_list)  
VALUES (value_list); 

Here is the basic syntax for the INSERT INTO SELECT command in MySQL. The following statement can be used to copy all data from one table to another:

INSERT INTO table_name2  
SELECT * FROM table_name1  
WHERE condition; 

In MySQL version 8.0.19, we can use a TABLE statement instead of a SELECT query to obtain the same result:

INSERT INTO table2 TABLE table1;  

In this case, TABLE table1 is equivalent to SELECT * FROM table1. This is used when we wish to add all records from the source table to the target table without filtering the values.

Example

Here is an example of how the INSERT INTO SELECT statement in MySQL works. The first step is to create a table named “employee” using the following statement:

CREATE TABLE employee (
emp_ID int AUTO_INCREMENT PRIMARY KEY,
Name varchar(45) DEFAULT NULL,
Email varchar(45) DEFAULT NULL,
County varchar(25) DEFAULT NULL
);

The next step is to insert values into the table. To add data to a table, we can execute the following statement:

INSERT INTO employee(emp_ID, Name, Email, County)
VALUES (1,'venu', 'venu@gmail.com', 'India'),
(2, 'madhu', 'madhu@gmail.com', 'US'),
(3, 'jhon', 'jhon@gmail.com', 'US');

The following SELECT statement can be used to verify the data:

SELECT * from employee;

i

We will now create a new table named employee_info that has the same number of columns and data types as the above table:

CREATE TABLE employee_info (
id int AUTO_INCREMENT PRIMARY KEY,
name varchar(45) NOT NULL,
email varchar(45) NOT NULL,
country varchar(25) NOT NULL
);

In the second step, we will insert persons located in Texas into the employee_info table using the INSERT INTO SELECT statement:

INSERT INTO employee_info (name, email, County)
SELECT Name, Email, County
FROM employee
WHERE County = 'US';

Using the SELECT query, we can verify that the insert operation was successful. In the following output, we will see that all persons located in the US County have been successfully inserted.

SELECT * from employee_info;

i