Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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

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.





Comments

  • 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.

  • 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.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.