EXPORT TO VERTICA

Exports table data from one Vertica database to another.

The source database must be no more than one major release behind the target database.

Syntax

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

Parameters

LABEL

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

database

The target database of the data to export. 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.

[schema.]target‑table

The table in 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.

[schema.]source‑table

The table that contains the data to export.

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 Vertica Database.

See Also