在MySQL插入从一个表数据到另一个(insert data from one table to a

我想读从一个表中的所有数据，并在另一个表中插入一些数据。我的查询

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

但我得到一个错误，

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

请帮我。

Answer 1:

您可以使用INSERT ... 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

Answer 2:

如果你想从一个表中的所有数据插入到另一个表有一个很简单的SQL

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

Answer 3:

它不会像这样工作。

当您尝试使用查询插入该行的所有值应该在那里查询。

对于上述问题，你要插入magazine_subscription_id, subscription_name, magazine_id, status在你已经选择查询magazine_subscription_id, subscription_name, magazine_id, status 1这是不可能的。

如果你想插入或者你需要使用直接值的查询，插入

Answer 4:

实际上是将数据复制到另一个MySQL查询从一个表
Insert into table2_name (column_names) select column_name from table1
其中，从表1中复制的值来TABLE2

Answer 5:

如果有喜欢的“ID”主键，你必须将它排除在外，例如我的PHP表有：ID，COL2，COL3，COL4列。 ID是主键，所以如果我运行此代码：

INSERT INTO php  (SELECT * FROM php);

我可能得到这个错误：

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

因此，这里的解决方案，我排除了“标识”键：

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

所以，我的新的PHP表不再拥有所有PHP2表行。

Answer 6:

试试这个。你在错误的方式做。

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

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

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

Answer 9:

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

但是，这是一个糟糕的MYSQL

做到这一点，而不是：

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

Answer 10:

INSERT INTO mt_magazine_subscription SELECT *
       FROM tbl_magazine_subscription 
       ORDER BY magazine_subscription_id ASC

文章来源: insert data from one table to another in mysql