Home

	Expand Search

Access Service User's Guide DirectConnect for AS/400

Chapter 10 Using Bulk Copy Transfer

Syntax for Bulk Copy transfer statements

Syntax for Bulk Copy transfer statements

The required syntax for a bulk copy transfer statement is:

transfer [with report]

{to | from} 'secondaryname
userid password';

with {insert | replace | truncate} into tablename;

sourceselectstatement

where:

• transfer is the command to initiate a data transfer.

• 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, after the transfer completes processing.

  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 transfer originates from the primary database and sends data to the secondary database.

  • from specifies that the data transfer originates from the secondary database and moves data to the primary database.

• 'secondaryname userid password' is a three-part character string, enclosed in single or double quotes in the order shown, that provides the information needed to connect to the secondary database:

  • secondaryname is the name used to identify the secondary database, which can be an access service connected to another database. This is the name of an entry in your platform-specific interface file, such as sql.ini in Windows.

  • userid and password must be valid on the secondary database. You can substitute an asterisk for password, if a password is not set.

• 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). The truncate option is equivalent to replace, except when the target is Adaptive Server, in which case the truncate option causes the DirectConnect to issue a truncate command against the target table, rather than delete.

  In a transfer to Adaptive Server, the replace option deletes rows in the target table. The truncate option truncates the target table (and is faster than delete), but a truncation cannot be rolled back if a subsequent error occurs. For all other targets, replace and truncate are equivalent.

• tablename specifies the table into which data is inserted or replaced.

• 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 as complex as the source database will accept, including stored procedures. SQL transformation is not performed on the sourceselectstatement. It must be in the source database's SQL dialect, such as Transact-SQL or AS/400 SQL.

Examples

This section contains some examples of bulk copy transfer statement syntax that are submitted through ISQL. The following table shows the database and user information used in the examples.

The following examples assume that the source and target databases are datatype-compatible.

Task: Transfer data from the AS/400 to append to an Adaptive Server table.

c:>isql -Sas4 - Uas4user -Pas4pass

1>transfer to 'ss ssuser sspass' ;

2>with insert into sstable1 ;

3>select * from as4table3 ;

4>go

Result: Data from the AS/400 table named as4table3 is appended to the Adaptive Server table sstable1

Task: Transfer data from Adaptive Server to replace data in the AS/400.

c:>isql -Sas4 -Uas4user -Pas4pass

1>transfer from 'ss ssuser sspass' ;

2>with replace into as4table1 ;

3>select * from sstable3 ;

4>go

Result: Data from the Adaptive Server table named sstable3 replaces the data in the AS/400 table as4table1.

Task: Transfer data from two tables in the AS/400 to a single Adaptive Server table.

c:>isql -Sas4 -Uas4user -Pas4pass

1>transfer to 'ss ssuser sspass' ;

2>with insert into sstable1 ;

3>select a.ssno, a.name2, b.empid

4>from as4table1 a, as4table2 b

5>where a.ssno=b.ssno ;

6>go

Result: A social security number (ssno), a name (name2), and an ID (empid) are selected from two AS/400 tables, based on a join of social security numbers. The information is appended to the Adaptive Server table called sstable1.