如何在MySQL中将数据从一个表复制到另一个新表?

I want to copy data from one table to another in MySQL.

我想在MySQL中将数据从一个表复制到另一个表。

Table 1 (Existing table):

表1(现有表):

aid    
st_id
from_uid
to_gid
to_uid
created
changed
subject
message
link

Table 2 (New Table)

表2(新表)

st_id
uid
changed
status
assign_status

I want to copy some fields of data from TABLE 1 into TABLE 2.

我想将表1中的一些数据字段复制到表2中。

Can this be done using MySQL queries?

可以使用MySQL查询完成吗?

9 个解决方案

#1

207

This will do what you want:

这将做你想做的:

INSERT INTO table2 (st_id,uid,changed,status,assign_status)
SELECT st_id,from_uid,now(),'Pending','Assigned'
FROM table1

If you want to include all rows from table1. Otherwise you can add a WHERE statement to the end if you want to add only a subset of table1.

如果要包含表1中的所有行。否则，如果只想添加表1的子集，可以在末尾添加WHERE语句。

I hope this helps.

我希望这可以帮助。

#2

If you don't want to list the fields, and the structure of the tables is the same, you can do:

如果您不想列出字段，并且表的结构是相同的，您可以这样做:

INSERT INTO `table2` SELECT * FROM `table1`;

or if you want to create a new table with the same structure:

或者如果您想创建一个具有相同结构的新表:

CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl;

Reference for insert select; Reference for create table select

参考插入选择;用于创建表选择的引用

#3

You can easily get data from another table. You have to add fields only you want.

您可以轻松地从另一个表中获取数据。您必须只添加您想要的字段。

The mysql query is:

mysql查询的方法是:

INSERT INTO table_name1(fields you want)
  SELECT fields you want FROM table_name2

where, the values are copied from table2 to table1

在哪里，值从表2复制到table1 ?

#4

The best option is to use INSERT...SELECT statement in mysql.

最好的选择是使用INSERT…在mysql SELECT语句。

http://dev.mysql.com/doc/refman/5.0/en/insert-select.html

#5

CREATE TABLE newTable LIKE oldTable;

Then, to copy the data over

然后，复制数据

INSERT INTO newTable SELECT * FROM oldTable;

#6

SELECT *
INTO newtable [IN externaldb]
FROM table1;

http://www.w3schools.com/sql/sql_select_into.asp

#7

INSERT INTO Table1(Column1,Column2..) SELECT Column1,Column2.. FROM Table2 [WHERE <condition>]

#8

You can try this code

您可以试试这段代码。

insert into #temp 
select Product_ID,Max(Grand_Total) AS 'Sales_Amt', Max(Rec_Amount) ,'',''
from Table_Name group by Id

#9

the above query only works if we have created clients table with matching columns of the customer

只有在我们创建了具有客户的匹配列的客户端表时，上述查询才有效

INSERT INTO clients(c_id,name,address)SELECT c_id,name,address FROM customer

#1

207