COPY FROM VERTICA

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

The source database must be no more than one major release behind the target database.

Syntax

COPY [[database.]schema-name.]target-table 
... [( target‑columns )]
... FROM VERTICA source‑database.[schema.]source‑table
... [( source‑columns )]
... [load-method]
... [STREAM NAME 'stream name']
... [NO COMMIT]

Parameters

`[database.]schema`	Specifies a schema, by default `public`. If schema is any schema other than `public`, you must supply the schema name. For example: myschema.thisDbObject If you specify a database, it must be the current database.
`target‑table`	The target table for the imported data. Vertica loads the data into all projections that include columns from the schema table.
target‑columns	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 column fillers as part of the column definition.
source‑database	The source database of the data to import. A connection to this database must already exist in the current session before starting the copy operation; otherwise Vertica returns an error. For details, see `CONNECT TO VERTICA`.
[schema.]source‑table	The table that is the source of the imported data. If schema is any schema other than `public`, you must supply the schema name.
source‑columns	A comma-delimited list of the columns in the source table to import. If omitted, all columns are exported. See Mapping Between Target and Source Columns below.
load-method	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. Vertica testing has shown that direct loads offer optimal performance in the widest range of scenarios. `TRICKLE`: Loads data only into WOS, suitable for frequent incremental loads. For databases created in Vertica releases ≥ 9.3, Vertica ignores load options and hints and defaults to a load method of DIRECT. You can configure this behavior with configuration parameter DMLTargetDirect. This option is invalid for external tables. For details, see Choosing a Load Method.
STREAM NAME	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.
NO COMMIT	Prevents `COPY` from committing its transaction automatically when it finishes copying data. For details, see Overriding COPY Auto Commit.

Privileges

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

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.

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

For more examples, see Copying Data from Another Vertica Database.