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

Important: The source database can be one major release behind the target database.


COPY [[database.]schema.]target‑table 
... [( target‑column[,…])]
... FROM VERTICA database.[schema.]source‑table
... [(source‑column[,…])]
... [load-method]
... [STREAM NAME 'stream name']



The table in the local database to store the copied data.


A target table column to store the copied data. If you specify target columns, COPY FROM VERTICA writes only to those columns. If you omit specifying target columns, Vertica writes to target table columns as described below, in Source and Target Column Mapping.

Note: You cannot use column fillers as part of the column definition.

database The source database of the data to copy. A connection to this database must already exist in the current session.

The table that is the source of the copied data.


A source table column to copy. If you specify source columns, only these columns are copied from the source table. If you omit specifying source columns, Vertica copies columns from the source table, as described below in Source and Target Column Mapping.


Specifies how to load data into the database, one of the following:

  • AUTO (default): Initially loads data into WOS, suitable for smaller bulk loads.
  • DIRECT: Loads data directly into ROS containers, suitable for large (>100 MB) bulk loads.
  • TRICKLE: Loads data only into WOS, suitable for frequent incremental loads.

This option is invalid for external tables.

For details, see Choosing a Load Method in the Administrator's Guide.


Specifies 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 Overriding COPY Auto Commit in the Administrator's Guide.


Connecting to the Source Database

Before you can import data from another database, you must establish a connection to the source database with CONNECT. See Copying Data from Another Vertica Database for details.

By default, COPY FROM VERTICA copies or imports data over the Vertica private network. Connecting to a public network requires some configuration. For information about using this statement to copy data across a public network, see Using Public and Private IP Networks.

The copy operation fails if either side of the connection is a single-node cluster installed to localhost, or you do not specify a host name or IP address.

Source and Target Column Mapping

You can optionally name a subset of source and target columns to participate in the copy operation. COPY FROM VERTICA attempts to match columns in the source table with corresponding columns in the destination table.

The following table compares the different combinations of naming source and target columns, and the requirements that pertain to each option.

Omit source columns Specify source columns
Omit target columns

Match all columns in source table to columns in target table.

The number of columns in the two tables can differ, but the target table must have at least as many columns as the source table.

Match named source table columns to target table columns.

The number of columns in the two tables can differ, but the target table must have at least as many columns as the number of specified source columns.

Specify target columns

Match source columns to the named target columns.

The number of named target columns must equal the number of columns in the source table.

Match named source columns to named target columns.

The number of source and target columns must be equal.

Node Failure During COPY

See Handling Node Failure During Copy/Export in the Administrator's Guide.


This example demonstrates connecting to another database, copying the contents of an entire table from the source database to an identically-defined table in the current database directly into ROS, and then closing the connection:

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

For more examples, see Copying Data from Another Vertica Database in the  Administrator's Guide.