Copy rows from one table onto another using INSERT query

Question

I have a table with 158 columns and 22,000 rows and I have another empty table where I want insert values dynamically based on the WHERE condition coming from the user. The SELECT query will look something like this:

SELECT * FROM mygrist_tables WHERE suic_att>=5 AND gender='M'

This gives me about 9,000 records back (say). I want to insert these records into another table (just this filtered data). Is it possible? Could anyone give me an idea of how the INSERT query would look like and whether I need to create another table with all those 158 cloumns or could the INSERT query dynamically create all those 158 columns? Also, could I do this using a view or would a table be essential? Thanks in advance!

Answer 1

It sounds like you want to run the above SELECT statement and INSERT the results into a new table that does not exist. If so, this should work:

SELECT * INTO YourNewTable
FROM mygrist_tables 
WHERE suic_att>=5 AND gender='M'

Assuming YourNewTable already existed, then you'd need to run INSERT INTO:

INSERT INTO YourNewTable 
SELECT * 
FROM mygrist_tables 
WHERE suic_att>=5 AND gender='M'

Optionally you may need to specify the columns in they are not the same.

EDIT - Rereading comments and realizing DB is MySQL, to create a new table from a SQL statement, you should use:

CREATE TABLE YourNewTable
SELECT *
FROM mygrist_tables 
WHERE suic_att>=5 AND gender='M';

http://dev.mysql.com/doc/refman/5.0/en/create-table.html