Copy rows from one table onto another using INSERT query [closed]

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

You can use the SELECT INTO syntax

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

But you won't be able to insert into a table that already exists like that. If your table already exists, you would use

INSERT INTO MyOldTable
([LIST OUT YOUR COLUMNS HERE])
SELECT [LIST OUT YOUR COLUMNS HERE] 
FROM mygrist_tables WHERE suic_att>=5 AND gender='M'

Answer 2

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