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!

Deal

INSERT INTO ... SELECT ... – Briarwood

Could you give me a query as such please? – Deal

No, I think you could work that out. – Briarwood

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

Pituitary

Actually, I do have just a P_ID column in the new table and that isn't present in the old table. How could I update this query? Because I get this error when I try running the query: Error Code: 1136. Column count doesn't match value count at row 1 – Deal

@SharadhaJayaraman -- INSERT INTO YourNewTable (Col1,Col2,etc.) SELECT Col1,Col2,etc FROM... -- You have to specify the column names in that case unfortunately... Best of luck. – Pituitary

All the 158 columns? :( – Deal

And would I have to re-create all the 158 columns in the new table? – Deal

@SharadhaJayaraman -- your comments are unfortunately not making sense -- you either have an existing table in which case you need to use INSERT INTO or you don't where you can use SELECT * INTO. If you use SELECT * INTO, it will create the table/columns for you. Might be easier to do this and then add your new column. If not, you can use this query to get all your column names (so you don't have to type them all out): select group_concat(column_name) from information_schema.columns where table_name = 'yourtable'. Good luck! – Pituitary

I don't have a pre-existing table now, so when I run this query, then the mysql editor gives me an error saying that the newTable doesn't exist :( – Deal

I mean the INSERT...SELECT * query .. Sorry for the ambiguity – Deal

@SharadhaJayaraman -- you should have what you need :-) Again though, if the table does not exist (you said you don't have a pre-existing table now), then use SELECT * INTO. Best of luck. Spend a little time playing with it -- you'll figure it out! – Pituitary

This also just to help people (like me) for whom SELECT * INTO doesn't work: CREATE TABLE newTable(select * from oldTable [WHERE..]) – Deal

@SharadhaJayaraman -- Now I realize why you were having issues. I edited the answer to help others as well. This whole time I didn't realize you were using MySQL (which does not support select * into table, but rather select * into variable). Glad you figured it out -- I knew you would! – Pituitary

Answer 2

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'

Licence

How could I hard-code all 158 columns in the [LIST OUT YOUR COLUMNS HERE]? Is there not a better way of doing this? – Deal

What I meant was that which @Pituitary mentioned in his answer... You would have to list out the columns if the table you are selecting from differs from the table you are inserting into. If they are the same, you may use * – Licence

That really sucks. I'm in a similar predicament, in which manually typing out 50 different column names seems to be a dumb expectation of MySQL. I guess percona's alter tool is the only thing that makes any sense in this world. – Aikoail

A mysql (or any really) table with 50+ collumns seems like its very own kind of dumb too. – Exposition