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 TO [ /*+ DIRECT */ ] VERTICA 
   database.[schema.]target‑table [ ( target‑columns ) ]
   { AS SELECT query‑expression | FROM [schema.]source‑table[ ( source‑columns ) ] };

Parameters

/*+ DIRECT */

In databases created in versions of Vertica prior to 9.3, specifies to bypass memory (WOS) and write target table data directly to disk (ROS). If you omit this hint, Vertica uses AUTO mode to load the data.

For databases created in Vertica releases ≥ 9.3, Vertica ignores load options and hints and defaults to a load method of DIRECT. You can configure this behavior with configuration parameter DMLTargetDirect.

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 to store the exported data.
target‑columns A comma-delimited list of columns in target‑table to store the exported data. See Mapping Between Source and Target Columns, below.
query‑expression

Specifies 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. If omitted, all columns are exported. See Mapping Between Source and Target Columns, below.

Privileges

  • 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

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