[mysql] insert data from one table to another in mysql

i want to read all data from one table and insert some data in to another table. my query is

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

but i got an error that

  #1136 - Column count doesn't match value count at row 1

please help me.

This question is related to mysql insert

The answer is


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

Actually the mysql query for copy data from one table to another is

Insert into table2_name (column_names) select column_name from table1

where, the values copied from table1 to table2


It wont work like this.

When you try to insert the row using a query all values should be there in query.

With the above problem you want to insert magazine_subscription_id, subscription_name, magazine_id, status in select query you have magazine_subscription_id, subscription_name, magazine_id, status 1 it is not possible.

If you want to insert either you need to insert using query of direct values


If there is a primary key like "id" you have to exclude it for example my php table has: id, col2,col3,col4 columns. id is primary key so if I run this code:

INSERT INTO php  (SELECT * FROM php);

I probably get this error:

#1062 - Duplicate entry '1' for key 'PRIMARY'

So here is the solution, I excluded "id" key:

INSERT INTO php ( col2,col3,col4)  (SELECT col2,col3,col4 FROM php2);

So my new php table has all php2 table rows anymore.


If you want insert all data from one table to another table there is a very simply sql

INSERT INTO destinationTable  (SELECT * FROM sourceDbName.SourceTableName);

INSERT INTO destination_table ( 
      Field_1, 
      Field_2, 
      Field_3) 
SELECT Field_1, 
      Field_2, 
      Field_3 
      FROM source_table;

BUT this is a BAD MYSQL

Do this instead:

  1. drop the destination table: DROP DESTINATION_TABLE;
  2. CREATE TABLE DESTINATION_TABLE AS (SELECT * FROM SOURCE_TABLE);

Try this. Your doing in wrong way.

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

INSERT INTO mt_magazine_subscription SELECT *
       FROM tbl_magazine_subscription 
       ORDER BY magazine_subscription_id ASC

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