MySQL 在mysql中将数据从一个表插入到另一个表

insert data from one table to another in mysql

提问by deepu sankar

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.

请帮我。

回答by Gustav Bertram

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

您可以使用INSERT...SELECT语法。请注意，您可以直接在 SELECT 部分引用“1”。

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 

回答by Ravi

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

如果要将一个表中的所有数据插入到另一个表中，则有一个非常简单的 sql

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

回答by Praveen

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, statusin select query you have magazine_subscription_id, subscription_name, magazine_id, status1 it is not possible.

对于上述问题，您想magazine_subscription_id, subscription_name, magazine_id, status在选择查询中 插入 magazine_subscription_id, subscription_name, magazine_id, status1，这是不可能的。

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

如果要插入，则需要使用直接值查询插入

回答by BARIS KURT

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:

如果有一个像“id”这样的主键，你必须排除它，例如我的 php 表有：id、col2、col3、col4 列。id 是主键，所以如果我运行此代码：

INSERT INTO php  (SELECT * FROM php);

I probably get this error:

我可能会收到此错误：

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

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

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

所以这是解决方案，我排除了“id”键：

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

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

所以我的新 php 表不再包含所有 php2 表行。

回答by php

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

实际上，用于将数据从一个表复制到另一个表的 mysql 查询是
Insert into table2_name (column_names) select column_name from table1
从 table1 复制到 table2 的值

回答by Guryash Singh

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

回答by Amit Garg

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

回答by arvin_codeHunk

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

回答by Naveen Rai

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

但这是一个糟糕的 MYSQL

Do this instead:

改为这样做：

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

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

回答by sudhanshu

INSERT INTO mt_magazine_subscription SELECT *
       FROM tbl_magazine_subscription 
       ORDER BY magazine_subscription_id ASC