COPY FROM VERTICA

Imports data from another Vertica database. COPY FROM VERTICA is similar to COPY, but supports only a subset of its parameters.

Syntax

COPY [[database.]schema-name.]target-table
    [( target-columns )]
    FROM VERTICA source-database.[schema.]source-table
    [( source-columns )]
    [STREAM NAME 'stream name']
    [NO COMMIT]

Parameters

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

*target-table*

The target table for the imported data. Vertica loads the data into all projections that include columns from the schema table.

*target-columns*

A comma-delimited list of columns in target-table to store the copied data.See Mapping Between Target and Source Columns below.

You cannot use FILLER columns or columns of complex types, except native arrays, as part of the column definition.

*source-database*

The source database of the data to import. 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.]source-table

The table that is the source of the imported data. If schema is any schema other than public, you must supply the schema name.

*source-columns*

A comma-delimited list of the columns in the source table to import. If omitted, all columns are exported.Columns cannot be of complex types. See Mapping Between Target and Source Columns below.

STREAM NAME

A COPY load stream identifier. Using a stream name helps to quickly identify a particular load. The STREAM NAME value that you specify in the load statement appears in the stream column of the LOAD_STREAMS system table.

NO COMMIT

Prevents COPY from committing its transaction automatically when it finishes copying data. For details, see Using transactions to stage a load.

Privileges

• Source table: SELECT

• Source table schema: USAGE

• Target table: INSERT

• Target table schema: USAGE

Mapping between target and source columns

If you copy all table data from one database to another, COPY FROM 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 COPY FROM VERTICA statement must include column lists that explicitly map target and source 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.

Node failure during COPY

See Handling node failure during copy/export.

Examples

The following example copies the contents of an entire table from the vmart database to an identically-defined table in the current database:

=> CONNECT TO VERTICA vmart USER dbadmin PASSWORD 'myPassword' ON 'VertTest01',5433;
CONNECT
=> COPY customer_dimension FROM  VERTICA vmart.customer_dimension;
 Rows Loaded
-------------
      500000
(1 row)
=> DISCONNECT vmart;
DISCONNECT

For more examples, see Copying data from another Vertica database.

See also