Is there an easy way to get the results of a SQL query into a new table?

In MySQL you can use the result of a select for the values of an insert.


mysql> create table t1 ( one int, two int, three char(10));
Query OK, 0 rows affected (0.09 sec)

mysql> create table t2 ( foo int, bar char(10));
Query OK, 0 rows affected (0.11 sec)

mysql> insert into t1 (one, two, three) values (0,0,'a'),(1,1,'a'),(1,1,'b');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> insert into t2 (foo, bar) select one, three from t1 where three = 'a';
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from t2;
+------+------+
| foo | bar |
+------+------+
| 0 | a |
| 1 | a |
+------+------+
2 rows in set (0.00 sec)

You can also create the table at the same time using the "create table <table> as select...". For example, if you have this as a table:

create table names (
   id integer,
   fname text,
   lname text);

insert into names (id, fname, lname) values (1,'fred','flintstone');
insert into names (id, fname, lname) values (2,'fred','jones');
insert into names (id, fname, lname) values (3,'fred','rightsaid');
insert into names (id, fname, lname) values (4,'barney','rubble');
insert into names (id, fname, lname) values (5,'fred','flintstone');
insert into names (id, fname, lname) values (6,'barney','rubble');
insert into names (id, fname, lname) values (8,'fred','flintstone');

You can use the following command to create a new table with the same structure that has just the data you want:

create table foo as select * from names where fname = 'fred';

One thing to be careful of, though -- the new table will not have any indexes, referential integrity rules, triggers, or anything else. So, if you're working with lot's of data, you'll still might need to create these on the new table.