Copying Data from Another Vertica Database

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;
CONNECT
  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 
-------------
      500000
(1 row)
=> DISCONNECT vmart;
DISCONNECT

    Successive COPY FROM VERTICA statements in the same session can import data from multiple tables over the same connection.

  3. DISCONNECT disconnects from the source database when all import and export operations are complete: 
    => DISCONNECT vmart;
DISCONNECT

    Closing your session also closes the database connection. However, it is a good practice to explicitly close the connection to the other database, both to free up resources and to prevent issues with other SQL scripts that might be running in your session. Always closing the connection prevents potential errors if you run a script in the same session that attempts to open a connection to the same database, since each session can only have one connection to a given database at a time.

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.