insert data from one table to another in mysql

Question

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

User · Accepted Answer

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

User · Answer

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

User · Answer

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    drop the destination table  DROP DESTINATION TABLE  CREATE TABLE DESTINATION TABLE AS  SELECT   FROM SOURCE TABLE

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

INSERT INTO mt magazine subscription SELECT          FROM tbl magazine subscription         ORDER BY magazine subscription id ASC

User · Answer

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

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

Examples related to mysql

Examples related to insert