How to Transfer Data from One Table to Another in MySQL Using INSERT...SELECT?

Inserting Data from One Table into Another in MySQL

Often, there's a need to transfer data from one table to another in MySQL. This can be achieved using various methods, but one of the simplest approaches is to use the INSERT...SELECT syntax.

Example

Suppose you have two tables, tbl_magazine_subscription and mt_magazine_subscription, and you want to copy data from the first table into the second. You can use the following query:

INSERT INTO mt_magazine_subscription ( 
      magazine_subscription_id, 
      subscription_name, 
      magazine_id, 
      status ) 
SELECT magazine_subscription_id, 
       subscription_name, 
       magazine_id, 
       '1'
FROM tbl_magazine_subscription
ORDER BY magazine_subscription_id ASC 

Error Handling

If you encounter the error "#1136 - Column count doesn't match value count at row 1," it means that the number of columns in the INSERT statement doesn't match the number of values in the SELECT statement. To resolve this error, ensure that the number of columns and values align.

The above is the detailed content of How to Transfer Data from One Table to Another in MySQL Using INSERT...SELECT?. For more information, please follow other related articles on the PHP Chinese website!