Cannot insert query results into SQL Query in phpMyAdmin

Question

I am running the following query:

SELECT *
INTO consolidated
FROM    (SELECT qtyAvailable
            FROM  Inventory
            UNION ALL
            SELECT revenue,
                   location,
                   qtySold
            FROM   sales
            UNION ALL
            SELECT inactive,
                   new,
                   itemNum,
                   category,
                   subCategory,
                   color,
            FROM Masterlist) n

And get these errors

    Error
SQL query: Documentation


SELECT *
INTO consolidated
FROM    (SELECT qtyAvailable
            FROM  Inventory
            UNION ALL
            SELECT revenue,
                   location,
                   qtySold
            FROM   sales
            UNION ALL
            SELECT inactive,
                   new,
                   itemNum,
                   category,
                   subCategory,
                   color,
            FROM Masterlist) n
 LIMIT 0, 25 
MySQL said: Documentation

#1327 - Undeclared variable: consolidated

and when I remove the new table or "variable" this error

Error
SQL query: Documentation


SELECT *
FROM    (SELECT qtyAvailable
            FROM  Inventory
            UNION ALL
            SELECT revenue,
                   location,
                   qtySold
            FROM   sales
            UNION ALL
            SELECT inactive,
                   new,
                   itemNum,
                   category,
                   subCategory,
                   color,
            FROM Masterlist) n
 LIMIT 0, 25 
MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM Masterlist) n
LIMIT 0, 25' at line 16

And when I remove the n, the similar error

Error
SQL query: Documentation


SELECT *
FROM    (SELECT qtyAvailable
            FROM  Inventory
            UNION ALL
            SELECT revenue,
                   location,
                   qtySold
            FROM   sales
            UNION ALL
            SELECT inactive,
                   new,
                   itemNum,
                   category,
                   subCategory,
                   color,
            FROM Masterlist)
 LIMIT 0, 25 
MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM Masterlist)
LIMIT 0, 25' at line 16

I am not deeply experienced with myPHP , but basically I am running a query on three tables in a database, and I want to insert the results in a new table, which I am calling consolidated. I created the consolidated table and left the columns empty. I do not know where to begin, thanks in advance for any help! Also I came up with the initial query based on this post

for inserting its `insert into` and when you want to insert by selecting its `insert into table select from...` Your syntax is wrong and you should provide some sample data and expected result.Also when you do union or union all the selects should have same num of columns which is again not so in your case. — Abhik Chakraborty, Sep 11 '15 at 07:00
you just refer this link http://blog.sqlauthority.com/2007/08/15/sql-server-insert-data-from-one-table-to-another-table-insert-into-select-select-into-table/ — vishuB, Sep 11 '15 at 07:03
You must check your union all query, Union all require same column list. — Indra Prakash Tiwari, Sep 11 '15 at 07:05
the expected results are just a fully populated consolidated table from the query, i tried insert into, but am still getting an error — kat, Sep 11 '15 at 07:08

Mureinik · Accepted Answer · 2015-09-11T07:24:58.847

0

You have a redundant comma before the last from clause which is causing the second error. Regardless, the the select ... into syntax isn't supported in MySQL. Instead, you should use the insert ... select syntax:

INSERT INTO consolidated 
SELECT *
FROM    (SELECT qtyAvailable
         FROM  Inventory
         UNION ALL
         SELECT revenue,
                location,
                qtySold
         FROM   sales
         UNION ALL
         SELECT inactive,
                new,
                itemNum,
                category,
                subCategory,
                color -- redundant comma removed here
         FROM Masterlist) n

edited Sep 11 '15 at 07:24

answered Sep 11 '15 at 07:03

Mureinik

297,002
52
306
350

awesome! that worked, however I am getting this now #1248 - Every derived table must have its own alias , I am pretty sure this is unrelated to the query schema, I may open a separate question for it if i cant find the answer here, thanks you very much for your help! – kat Sep 11 '15 at 07:11
@kat I mistakenly removed the `n` alias (edited and fixed). If this is happening on a different query, even if it's in the same script, please open a new question. – Mureinik Sep 11 '15 at 07:26

Cannot insert query results into SQL Query in phpMyAdmin

1 Answers1