creating temp table as select using vsql does not load data into table

Tara_1

I am trying to create temp table as select using vsql as shown below:
[dbadmin procedures]$/opt/vertica/bin/vsql -c "CREATE TEMP TABLE TMP_TECH ON COMMIT PRESERVE ROWS AS
             SELECT A.DRIVE_ID,A.DEVICE_ID,A.DEVICE_TECH_ID
               FROM CALL_DATA.LU_MARKET_CARRIER A
                    INNER JOIN CALL_DATA.STG_CURRENT_DRIVES B
                 ON A.DRIVE_ID            = B.DRIVE_ID
           GROUP BY A.DRIVE_ID,A.DEVICE_ID,A.DEVICE_TECH_ID
      SEGMENTED BY HASH(DRIVE_ID,DEVICE_ID,DEVICE_TECH_ID) ALL NODES;" -d Nielson_db -U dbadmin -w password

The table is created but no data loaded into it. While after connecting to database and then running below query creates and loads the data into temp table:

Example_db=>CREATE TEMP TABLE TMP_TECH ON COMMIT PRESERVE ROWS AS
             SELECT A.DRIVE_ID,A.DEVICE_ID,A.DEVICE_TECH_ID
               FROM CALL_DATA.LU_MARKET_CARRIER A
                    INNER JOIN CALL_DATA.STG_CURRENT_DRIVES B
                 ON A.DRIVE_ID            = B.DRIVE_ID
           GROUP BY A.DRIVE_ID,A.DEVICE_ID,A.DEVICE_TECH_ID
      SEGMENTED BY HASH(DRIVE_ID,DEVICE_ID,DEVICE_TECH_ID) ALL NODES;

Please let me know what may be reason.

Sharon_Cutter

The data in a temporary table is session-specific.  When you load the table using vsql -c, the session ends when the command completes.  ON COMMIT PRESERVE ROWS controls whether the data is transaction or session scoped, but it's still session-specific.

Tara_1

Thanks for your reply. My requirement is to make external procedure which contains few temp table creation. Could you please tell me what can be the possible workaround to fullfil my requirement.