MySQL copy the contents of one table into another
MySQL is a popular open-source relational database management system that allows users to store and retrieve data from a database. The INSERT INTO command is used to insert data into a MySQL database, while the SELECT statement is used to retrieve data from a database. In this blog post, we will explore the MySQL command INSERT INTO tableA SELECT DISTINCT * FROM tableB“.
Explanation:
The “INSERT INTO tableA SELECT DISTINCT * FROM tableB;” command is used to insert unique rows from tableB into tableA. Let’s break down this command to understand its components:
INSERT INTO tableA: This component specifies the table that we want to insert data into. In this case, we are inserting data into tableA.SELECT DISTINCT: This component selects all the columns () from tableB and removes any duplicates using the DISTINCT keyword.FROM tableB: This component specifies the table from which we want to select data. In this case, we are selecting data from tableB.
Putting it all together, the command INSERT INTO tableA SELECT DISTINCT * FROM tableB; selects all the unique rows from tableB and inserts them into tableA.
Let’s look at an example to understand this command better. Suppose we have two tables, tableA and tableB, with the following data:
tableA:
| id | name | age |
|---|---|---|
| 1 | John | 20 |
| 2 | Peter | 25 |
tableB:
| id | name | age |
|---|---|---|
| 1 | John | 20 |
| 2 | Peter | 25 |
| 3 | Sarah | 30 |
| 4 | Rachel | 35 |
If we run the command INSERT INTO tableA SELECT DISTINCT * FROM tableB;, it will insert the unique rows from tableB into tableA. Since tableA already contains the rows for John and Peter, only the rows for Sarah and Rachel will be inserted into tableA. Therefore, the resulting tableA will look like:
tableA:
| id | name | age |
|---|---|---|
| 1 | John | 20 |
| 2 | Peter | 25 |
| 3 | Sarah | 30 |
| 4 | Rachel | 35 |
Conclusion:
The MySQL command “INSERT INTO tableA SELECT DISTINCT * FROM tableB;” is a powerful tool for inserting unique rows from one table into another. It can be helpful when you have two tables with overlapping data and want to consolidate the data into a single table. Understanding how this command works allows you to manage your MySQL databases and optimize your data storage effectively.
