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.

回答1:

You can use INSERT...SELECT syntax. Note that you can quote '1' directly in the SELECT part.

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 

回答2:

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);

回答3:

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

回答4:

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

回答5:

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.

回答6:

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)
    )

回答7:

  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))

回答8:

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));

回答9:

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);

回答10:

INSERT INTO mt_magazine_subscription SELECT *
       FROM tbl_magazine_subscription 
       ORDER BY magazine_subscription_id ASC