Destination-Template Transfer Processing

During a destination-template transfer, the access service inserts the data values it retrieves with the sourceselectstatement from the source database into the destination-template SQL clause.

This clause contains one question mark for each column in the result set of the sourceselectstatement. Each value from the result set is substituted for the corresponding question mark in the template on a row-by-row basis. The access service executes the resulting statement against the target database.When you use the destination-template transfer statement, these restrictions apply:

The transfer statement must be the only statement in a request.
The table into which you want to transfer data (the target) must already exist. The transfer statement does not create new tables in the transfer target.
The structure of the target table must match the structure of the source table.
For any transfer to work, the ConnectionSpec to the secondary connection must be recorded in the sql.ini file for Windows or in the interfaces file for UNIX.

Warning! The DirectConnect server cannot correctly transfer varchar values containing empty strings (zero length non null strings) in the destination-template transfer process. Empty string varchar values are interpreted as NULL values.

Syntax

transfer [with report]

{to | from} ‘secondaryname userid password’;

sourceselectstatement;

destinationtemplatestatement

where:

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

This information is returned as a result set that consists of one VARCHAR column and one row. The row contains the number of rows transferred, rejected, or 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 character string that provides the information needed to connect to the secondary database:
- secondaryname is the name used to identify the secondary database.
- userid and password must be valid on the secondary database. You can substitute an asterisk for password, if a password is not specified.
All of the elements of the character string must be enclosed in single or double quotes in the sequence.
sourceselectstatement specifies a SQL statement that is executed against the source database to produce the result set that will be used in the transfer. This statement can be of any complexity acceptable to the source database, including stored procedures. SQL transformation is not performed on the sourceselectstatement. The transformation must be in the source database SQL syntax.
destinationtemplatestatement is a SQL statement or any statement that is valid for the target database environment where it executes. SQL transformation is not performed on the destinationtemplatestatement. The transformation must be in the destination database SQL syntax.

This statement can include question marks as placeholders for the data values that will be inserted. It can also include qualifiers to specify datatypes for the question mark placeholders in the destinationtemplatestatement.

To increase processing efficiency, you can batch destination-templates together for processing. Use the TransferBatch configuration property or a set statement to specify the number of templates to batch.