Copying Data from Another Vertica Database

You can import a table or specific columns in a table from another Vertica database. The table receiving the copied data must already exist, and have columns that match (or can be coerced into) the data types of the columns you are copying from the other database. You can import data from an earlier Vertica release, if the earlier release is a version of the last major release before the target database release.

Import Process

Importing is a three-step process:

  1. CONNECT connects to the database that contains the data to import.

    The import 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.

  2. COPY FROM VERTICA imports table data to the target database. To import data from multiple tables, issue multiple COPY FROM VERTICA statements using the same connection to the source database.
  3. DISCONNECT disconnects from the source database when the copy operation is complete.

Importing Identity Columns

You can import identity (and auto-increment) 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.

    Failure to list which identity columns to export can cause an error, because the identity column will be interpreted as missing in the destination table.

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 (and Auto-increment) columns by specifying them directly in the source table. To disable this behavior globally, set the CopyFromVerticaWithIdentity configuration parameter, described in Configuration Parameters.

Examples

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 '' ON 'VertTest01',5433;
CONNECT
=> COPY customer_dimension FROM  VERTICA vmart.customer_dimension DIRECT;
 Rows Loaded 
-------------
      500000
(1 row)
=> DISCONNECT vmart;
DISCONNECT

This example demonstrates copying several columns from a table in the source database into a table in the local database:

=> CONNECT TO VERTICA vmart USER dbadmin PASSWORD '' ON 'VertTest01',5433;
CONNECT
=> COPY people (name, gender, age) FROM VERTICA  
   vmart.customer_dimension (customer_name, customer_gender,
   customer_age);
 Rows Loaded 
-------------
      500000
(1 row)
=> DISCONNECT vmart;
DISCONNECT