I am trying to put into a table the output from a previous query (saved as a volatile table). I am creating a new table PROC_STATS to hold the output and then using INSERT INTO followed by a SELECT query to get the data inot PROC_STATS. When I run the code in SQL Assistant, I get a message that 1 row was returned, which is the correct value. However, when I run SELECT * FROM PROC_STATS, no rows are returned. What gives? Here is my code:
CREATE VOLATILE TABLE proc_Stats (Ref_Month VARCHAR(10), Proc_Step INT
,File_Name VARCHAR(30), Rec_Cnt FLOAT);
INSERT INTO Proc_Stats (ref_month, proc_step, file_name , rec_cnt)
SELECT elig_month, proc_step, file_name , rec_cnt
I know that when I create a volatile table, I need to include the WITH DATA ON COMMIT PRESERVE ROWS, but that generates an error message here that there needs to be something between DATA and ON. If I add PRIMARY INDEX (..) after the WITH DATA, I get a different error about something being expected between DATA and PRIMARY.
I appreciate your advice.
The default for COMMIT is DELETE ROWS which happens when the Inserts is commited :-)
When you create a Volatile Table you don't need the WITH DATA.
And when you want to use CREATE VOLATILE TABLE AS you need to specify the right order:
WITH DATA PRIMARY INDEX () ON COMMIT PRESERVE ROWS