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

Export not working with Local Temp Tables

Hi,

I tried to export one table data from our PROD vertica DB to UAT vertica DB using some local temp table but it is not working.

The connect statement works fine only the problem with export

Query:

CREATE LOCAL TEMP TABLE EMPLOYEE
(
EMP_ID INT,
DOJ DATE
)
ON COMMIT PRESERVER ROWS;

INSERT INTO EMPLOYEE
SELECT EMP_ID, DOJ FROM TGT_SCH.EMPLOYEE_DATES LIMIT 10;

EXPORT TO VERTICA UAT_DB.SCHM.EMPLOYEE AS SELECT A.* FROM TGT_SCH.EMPLOYEE A INNER JOIN EMPLOYEE B on A.EMP_ID = B.EMP_ID;

EXPORT TO VERTICA UAT_DB.SCHM.EMPLOYEE_SALARY AS SELECT A.* FROM TGT_SCH.EMPLOYEE_SALARY A INNER JOIN EMPLOYEE B on A.EMP_ID = B.EMP_ID;

Can someone pls help.

Comments

  • Hi!


    RTFM - click on link and read an article, please.

    http://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/AdministratorsGuide/Tables/AboutTemporaryT...
    Global Temporary Tables

    HP Vertica creates global temporary tables in the public schema, with the data contents private to the transaction or session through which data is inserted.

    Global temporary table definitions are accessible to all users and sessions, so that two (or more) users can access the same global table concurrently. However, whenever a user commits or rolls back a transaction, or ends the session, HP Vertica removes the global temporary table data automatically, so users see only data specific to their own transactions or session.

    Global temporary table definitions persist in the database catalogs until they are removed explicitly through a DROP TABLE statement.

    Local Temporary Tables

    Local temporary tables are created in the V_TEMP_SCHEMA namespace and inserted into the user's search path transparently. Each local temporary table is visible only to the user who creates it, and only for the duration of the session in which the table is created.

    When the session ends, HP Vertica automatically drops the table definition from the database catalogs. You cannot preserve non-empty, session-scoped temporary tables using the ON COMMIT PRESERVE ROWS statement.

    Creating local temporary tables is significantly faster than creating regular tables, so you should make use of them whenever possible.

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.