Insert records from multiple tables in MySQL

To insert records from multiple tables, use INSERT INTO SELECT statement. Here, we will insert records from 2 tables.

Let us first create a table −

mysql> create table DemoTable1943
   (
   Name varchar(20)
   );
Query OK, 0 rows affected (0.00 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1943 values('Chris');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1943 values('Robert');
Query OK, 1 row affected (0.00 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1943;

This will produce the following output −

+--------+
| Name   |
+--------+
| Chris  |
| Robert |
+--------+
2 rows in set (0.00 sec)

Here is the query to create second table −

mysql> create table DemoTable1944
   (
   Age int
   );
Query OK, 0 rows affected (0.00 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1944 values(23);
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1944 values(26);
Query OK, 1 row affected (0.00 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1944;

This will produce the following output −

+------+
| Age  |
+------+
|   23 |
|   26 |
+------+
2 rows in set (0.00 sec)

Here is the query to create third table −

mysql> create table DemoTable1945
   (
   StudentName varchar(20),
   StudentAge int
   );
Query OK, 0 rows affected (0.00 sec)

Here is the query to insert from multiple tables −

mysql> insert into DemoTable1945(StudentName,StudentAge) select tbl1.Name,tbl2.Age from DemoTable1943 tbl1,DemoTable1944 tbl2;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

Display all records from the table using select statement −

mysql> select * from DemoTable1945;

This will produce the following output −

+-------------+------------+
| StudentName | StudentAge |
+-------------+------------+
| Chris       |         23 |
| Robert      |         23 |
| Chris       |         26 |
| Robert      |         26 |
+-------------+------------+
4 rows in set (0.00 sec)