Syntax

Here is the required syntax for a bulk copy and express transfer statement:

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 data source 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).

  When transferring data to ASE, 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).

  You must use this transfer syntax carefully: Errors will render the table useless, and restoring it must be handled 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 will accept, including stored procedures. SQL transformation is not performed on the sourceselectstatement. It must be in the source database SQL dialect.