On page 137 of the Base handbook there is reference to SELECT [INTO CACHED|TEMP|TEXT] “New+table”]
When I enter a line like SELECT * from “members” WHERE “memnum” >180 INTO TABLE “check” I get an error complaining about the INTO Token. What is the correct way to use this feature.
I assume you are using the embedded HSQLDB engine.
Review the HSQLDB 1.8 guide at http://www.hsqldb.org/doc/guide/ch09.html#select-section
I did some experiments.
These statements are inserted into the SQL tool (Tools/SQL).
View/Refresh Tables to view the newly created tables.
SELECT * INTO TEXT OUTFILE FROM DTDIST WHERE DISTANCE = 1.0;
Produces a file OUTFILE.csv with the selected data, and a link to that file in base
SELECT * INTO CACHED OUTPUT FROM DTDIST WHERE DISTANCE = 1.0;
Produces a new table in base called OUTPUT with the data.
SELECT * INTO TEMP TFILE FROM DTDIST WHERE DISTANCE = 1.0;
The statment executes but no TFILE is found even after tables refresh.
Repeating the SELECT command gives table already exists error.
I can execute SELECT * FROM TFILE; but no output is shown in the sql editor.
In Queries the SELECT statement gives a table with headings but no data;
Same result even after the base file is closed and reopened.
Examining the script in the unzipped base file shows that there is a table creation statement
CREATE GLOBAL TEMPORARY MEMORY TABLE TFILE(“ID” INTEGER,“DISTANCE” NUMERIC(100,4),“TIME” TIME), but no associated data.
So for you the statement
SELECT * INTO CACHED "check" FROM "members" WHERE "memnum" > 180;
should work.
Many thanks, this does indeed work, exactly as I wanted it.
I still find the syntax of SQL awkward and in this case, as in so many in my experience of coding, once I can am told the answer it is so obvious.
PLease use comments and upvoting/marking the answer as correct to show thanks instead of adding an answer to this question. https://wiki.documentfoundation.org/Ask/Getting_Started