Export not working with Local Temp Tables

Arumugaguru_M

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.

id10t

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.