INSERT INTO new table from existing query output

Teradata Applications

INSERT INTO new table from existing query output

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

FROM Temp1;

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.

1 REPLY
Junior Contributor

Re: INSERT INTO new table from existing query output

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

Dieter