Bulk-Copy Transfer and Express Transfer

Bulk-copy and express transfers initiate a direct transfer of data between two databases from the client application.

You can use a bulk-copy or express transfer statement to copy large amounts of data between similar tables. The express transfer feature transfers data faster than bulk-copy transfer, and because it uses the same syntax as the bulk-copy transfer, you can use it without modifying your applications.

Syntax

transfer [with report]

{to | from } 'secondaryname userid password';

with {insert | replace | truncate| alter table} into tablename;

sourceselectstatement

where:

transfer must begin all transfer statements.
with report is an optional phrase specified in the first line of the transfer statement. It instructs the access service to return processing information to the client application.

This information is returned as a result set consisting of a VARCHAR column and a single row. The row contains the number of rows transferred, rejected, and modified during processing.
{to | from} indicates the direction of the transfer:
- to specifies that the data is transferred from the primary database to the secondary database.
- from specifies that the data is transferred from the secondary database to the primary database.
secondaryname userid password is a three-part character string that provides the information needed to connect to the secondary database:
- secondaryname is the name used to identify the secondary database and must be recorded in these files:
  - For bulk-copy transfer, in the UNIX interfaces file or in the Windows sql.ini file.
  - For express transfer, the secondaryname must match a datasource name (DSN) in the odbc.ini file.
- userid and password must be valid on the secondary database. If the password is NULL, you can substitute an asterisk for password and it will be corrected to a NULL when sent to the secondary connection. Exactly three tokens are sent to the secondary connection.
All of the elements of the character string must be enclosed in single or double quotes in the order shown.
with {insert | replace | truncate} into specifies whether the data is appended onto the target table (insert) or the existing data is deleted and replaced (replace or truncate).

Note: When transferring data to Adaptive Server, the truncate option causes transfer to issue a truncate rather than a delete against the target table. For other target databases, delete and truncate are equivalent.
{with alter table} into invokes a UDB command that disables logging for the transaction and truncates the table (for DB2 UDB only).

Note: You must use this transfer syntax carefully: Errors render the table useless, and requiring restoration according to IBM’s procedures. Read the IBM documentation pertaining to the alter table command and its option called activate not logged initially.
tablename specifies the table into which data is inserted or replaced. The table must already exist because the transfer statement does not create a new one in the target database.
sourceselectstatement specifies a SQL statement that is executed against the source database to produce the result set used in the transfer.

This statement can be any statement the source database accepts, including stored procedures. SQL transformation is not performed on the sourceselectstatement. It must be in the source database SQL dialect.