Imports data from another Vertica database.

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


COPY [[{namespace. | database. }]schema.]target-table
    [( target-columns )]
    FROM VERTICA {source-namespace. | source-database. }[source-schema.]source-table
    [( source-columns )]
    [STREAM NAME 'stream name']


{ namespace. | database. }
Name of the database or namespace that contains table:
  • Database name: If specified, it must be the current database. In Eon Mode databases, the database name is valid only if the object is in the default namespace.
  • Namespace name (Eon Mode only): You must specify the namespace of objects in non-default namespaces. If no namespace is provided, Vertica assumes the object is in the default namespace.

You cannot specify both a database and namespace name.

Name of the schema, by default public. If you specify the namespace or database name, you must provide the schema name, even if the schema is public.
The target table for the imported data. Vertica loads the data into all projections that include columns from the schema table.
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. | [source-namespace.] }
The source of the data to import, which depends on the mode of your database:
  • Eon Mode: specify the namespace in the source database that contains the data to import, by default default_namespace.
  • Enterprise Mode: specify the source database name.

A connection to the source database must already exist in the current session before starting the copy operation; otherwise Vertica returns an error. For details, see CONNECT TO VERTICA.

The table that is the source of the imported data. If schema is any schema other than public or if you specified a source-namespace, you must supply the schema name.
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.
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.
Prevents COPY from committing its transaction automatically when it finishes copying data. For details, see Using transactions to stage a load.


  • 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.


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;
=> COPY customer_dimension FROM  VERTICA vmart.customer_dimension;
 Rows Loaded
(1 row)
=> DISCONNECT vmart;

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

See also