Copying data from another Vertica database

COPY FROM VERTICA imports table data from one Vertica database to another.

COPY FROM VERTICA imports table data from one Vertica database to another. The following requirements apply:

  • You already opened a connection to the target database with CONNECT TO VERTICA.

  • The source database is no more than one major release behind the target database.

  • The table in the target database must exist.

  • Source and target table columns must have the same or compatible data types.

Import process

Importing is a three-step process:

  1. Connect to the source database with CONNECT TO VERTICA. For example:

    => CONNECT TO VERTICA vmart USER dbadmin PASSWORD '' ON 'VertTest01',5433;
  2. Import the desired data with COPY FROM VERTICA. For example, the following statement imports all table data in customer_dimension to a table of the same name:

    => COPY customer_dimension FROM  VERTICA vmart.customer_dimension;
     Rows Loaded
    (1 row)
    => DISCONNECT vmart;
  3. DISCONNECT disconnects from the source database when all import and export operations are complete:

    => DISCONNECT vmart;

Importing IDENTITY columns

You can import IDENTITY columns as follows:

  • If both source and destination tables have an IDENTITY column and configuration parameter CopyFromVerticaWithIdentity is set to true (1), you do not need to list them.

  • If source table has an IDENTITY column, but target table does not, you must explicitly list the source and target columns.

After importing the columns, the IDENTITY column values do not increment automatically. Use ALTER SEQUENCE to make updates.

The default behavior for this statement is to import IDENTITY columns by specifying them directly in the source table. To disable this behavior globally, set the CopyFromVerticaWithIdentity configuration parameter.