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.

200937 次浏览
小开

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

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

小开
最佳答案

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

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

小开 
INSERT INTO mt_magazine_subscription SELECT *
FROM tbl_magazine_subscription
ORDER BY magazine_subscription_id ASC
小开

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

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

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.

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

Try to use this

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 ;

Use the hard coded value in select clause

小开

Insert data from one table to other with condition in MySQL and same will work in SQL Server as well. Only non existing data will get updated. Both table have same structure so column need not to pass.

insert into table_A
select * from table_A_copy
where not exists
(
select * from table_A where table_A_copy.clm_a=table_A.clm_a and table_A_copy.clm_b=table_A.clm_b and table_A_copy.clm_c=table_A.clm_c
);

提交答案