All Forums Teradata Applications
BBerszoner 1 post Joined 08/12
16 Apr 2013
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.
 

dnoeth 4628 posts Joined 11/04
16 Apr 2013

 
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

Dieter

You must sign in to leave a comment.