select into table

[mattgoody]

is it possible to insert a select query into a table without actually creating the table first? i know about "INSERT INTO TABLE SELECT" blah blah, but i think that in order for that to work i have to create the table initially with all the right columns. what i want is for mysql to automatically create the table with all the columns i select with my query. if its possible, how would i do it?

-Matt

[russell]

apparently Not (http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-select-into-table.html)

This is pretty easy in MSSQL, but I guess that's beside the point. I'm no mySQL expert, but I think u can select to file, then create table from that file...

[mattgoody]

darn.

[chazzy]

you can also do this...


CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_option ...]
select_statement


while it's not exactly what you're saying, it does create and populate the table based on the results of a query.

[mattgoody]

right, but i think that forces me to define the columns before selecting. i think, if im not mistaken, that i can do that also by just creating the table and then using "INSERT INTO TABLE SELECT..." oh well il figure something out. thanks guys, u always seem to have an answer :-)

-Matt

[chazzy]

I just checked it. The following works perfectly:


CREATE TABLE otherusers select * from users;
select * from otherusers;

[mattgoody]

thanks a ton chazzy, that was too obvious haha