EXPORT TO VERTICA

Exports table data from one Vertica database to another.

Exports table data from one Vertica database to another. A connection to the target database must exist in the current session before starting the copy operation. Otherwise, Vertica returns an error. For details, see CONNECT TO VERTICA.

Syntax

EXPORT [ /*+LABEL (label-string)*/ ] TO VERTICA
   { database. | [namespace.] }[schema.]target-table [ ( target-columns ) ]
   { AS SELECT query-expression | FROM [schema.]source-table[ ( source-columns ) ] }

Arguments

LABEL

Assigns a label to a statement to identify it for profiling and debugging.

namespace
For Eon Mode databases, namespace in the target database to which data is exported. If unspecified, the target namespace is set to default_namespace.
database
For Enterprise Mode databases, the target database to which data is exported. A connection to this database must already exist in the current session before starting the copy operation; otherwise Vertica returns an error. For details, see CONNECT TO VERTICA.
target-table
The table in the target database to store the exported data. The table cannot have columns of complex data types other than native arrays.
target-columns
A comma-delimited list of columns in target-table in which to store the exported data.See Mapping Between Source and Target Columns, below.
query-expression
The data to export.
[namespace.][schema.]source-table
The table in the source database that contains the data to export. The namespace option only applies to Eon Mode databases. If no namespace is specified, Vertica assumes the table belongs to the default_namespace.
source-columns
A comma-delimited list of the columns in the source table to export. The table cannot have columns of complex data types.See Mapping Between Source and Target Columns, below.

Privileges

Non-superusers:

  • Source table: SELECT

  • Source table schema: USAGE

  • Target table: INSERT

  • Target table schema: USAGE

Mapping between source and target columns

If you export all table data from one database to another, EXPORT TO VERTICA can omit specifying column lists if column definitions in both tables comply with the following conditions:

  • Same number of columns

  • Identical column names

  • Same sequence of columns

  • Matching or compatible column data types

  • No complex data types (ARRAY, SET, or ROW), except for native arrays

If any of these conditions is not true, the EXPORT TO VERTICA statement must include column lists that explicitly map source and target columns to each other, as follows:

  • Contain the same number of columns.

  • List source and target columns in the same order.

  • Pair columns with the same (or compatible) data types.

Examples

See Exporting data to another database.

See also