How to Insert Multiple Columns from Multiple Rows into Another MySQL Table

Learn how to efficiently copy multiple columns from various rows in one MySQL table to another by using the `INSERT...SELECT` statement.
---

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: MySql: How do I insert multiple columns from multiple rows into another table?

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Insert Multiple Columns from Multiple Rows into Another MySQL Table

In the world of database management, transferring data between tables is a common task. You might want to copy specific columns from multiple rows in a source table and create new rows in a destination table. However, if this is your first time attempting this, you could run into some roadblocks. Here's how to handle that successfully in MySQL.

The Problem

Let’s say you have a source table called source_table that has a few rows of data, and you want to copy specific columns from several of those rows into another table called destination_table. You might assume you can use the classic INSERT...VALUES statement, chaining together multiple SELECT statements for each row you wish to copy, like so:

[[See Video to Reveal this Text or Code Snippet]]

Unfortunately, this results in an error: ERROR 1241 (21000): Operand should contain 1 column(s). This error occurs because the way you structured the VALUES clause is incorrect; it expects each SELECT to produce a single value, not multiple columns.

The Solution: Using INSERT...SELECT

The good news is that there is a straightforward solution using the INSERT...SELECT syntax. This allows you to insert the results of a query directly into the destination table. Here is how you can do it:

The Correct SQL Statement

[[See Video to Reveal this Text or Code Snippet]]

Explanation of How It Works

INSERT INTO destination_table: This indicates the table where you want to add new rows.

(source_id, foo_id, bar): This lists the columns in which you will insert the data.

SELECT id, foo_id, bar FROM source_table: This selects the specific columns you want to copy from the source_table.

WHERE id IN (...): This part specifies the rows you wish to select by listing their IDs.

By using INSERT...SELECT, MySQL knows to pull the specified columns (id, foo_id, bar) from source_table for every row that matches the IDs in the IN clause and inserts them into destination_table.

Conclusion

Transferring data from one table to another in MySQL can be done efficiently and without error if you use the right approach. The INSERT...SELECT statement simplifies the process and helps you avoid common pitfalls related to incorrect value insertion syntax.

Now you can confidently create multiple rows in your destination table using values from your source table without running into errors. Happy querying!