Populating a table from query results (mysql)

Question

I would like to fill a table with the results of a query on existing table. How can I do that?

Answer 1

insert into table_name ...
select * from table_name where ....

The target table and the source query must match in number of columns and datatypes

See this link

Answer 2

(You don't need to match the table schemas)

INSERT tbl_name [col1, col2] SELECT value1, value2 FROM othertable

Answer 3

You can even create tables this way, though there the column names must match, or the select results are put in automatically added columns:

mysql> create table foo ( id int primary key auto_increment, bar datetime )
    -> select now() as bar, now() as baz from dual;
Query OK, 1 row affected, 1 warning (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from foo;
+----+---------------------+---------------------+
| id | bar                 | baz                 |
+----+---------------------+---------------------+
|  1 | 2009-03-10 17:01:35 | 2009-03-10 17:01:35 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)