Home

	Expand Search

Access Service User's Guide DirectConnect Anywhere

Chapter 10 Using Bulk Copy Transfer

Chapter 10

Using Bulk Copy Transfer

This chapter describes the bulk copy transfer process. It covers the following topics:

• Overview

• Bulk Copy Transfer processing

• Syntax

• Datatype conversion for transfer from statements

• Datatype conversion for transfer to statements

• Processing Bulk Copy values

• Bulk Copy Transfer errors

Overview

Bulk copy transfer initiates a direct transfer of data between two databases from the client application. You use the bulk copy transfer statement to copy large amounts of data between similar tables.

When you use a bulk copy transfer statement, the following 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.

Bulk Copy Transfer processing

Bulk copy transfer processing occurs in the following sequence:

• The client application initiates a transfer request.

• The access service receives the transfer request and executes the sourceselectstatement against the source database to retrieve the schema of the result set, including column datatypes, length, precision, and scale.

• The access service queries the target table for a description of the target table columns and compares this information to the structure of the result set for these criteria:

  • The target table must have at least as many columns as the result set.

  • The datatype of each result set column must be convertible to the type of its target column.

  If either of these tests fails, the access service stops processing the transfer and issues an error message.

• If the transfer statement includes the with replace or truncate clause, the access service deletes data in the target table, provided the user ID of the person executing the request is authorized to do so. If the user ID is not authorized, the transfer fails.

• The access service maps the columns from the result set to the columns in the target table in the order in which they appear. The access service attempts to insert NULL values (if allowable) in all columns in the target table that do not have corresponding columns in the result set.

• The access service prepares an insert or equivalent bulk load statement for execution against the target table.

• If conversion errors occur as rows are inserted (for example, a value is out of range), the invalid rows are handled according to the values set in the following properties:

  • CharConvertError

  • NumConvertError

  • DatetimeConvertError

  • DefaultDate

  • DefaultTime

  • DefaultNum

• The transfer continues processing. If the SendWarningMessages property is set to yes, the access service sends a warning message to the client application.

Syntax

The required syntax for a bulk copy transfer statement is as follows:

transfer [with report]

{to | from} 'secondaryname  userid password';

with {insert | replace | truncate} 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.

  • 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 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.

• 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 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 SQL dialect.

Datatype conversion for transfer from statements

Use transfer from when you want to transfer data from a secondary database to the primary database. The primary database, ODBC, is the target. The secondary database is specified in the transfer statement.

The access service performs datatype conversions for bulk copy transfer from statements. These conversions are defined within the access service and are not affected by the settings of the datatype conversions configuration properties.

The following table shows the acceptable Open Server datatypes that the access service can convert into corresponding ODBC datatypes. To fit the items in the table, the CS_ prefix is removed from the datatypes, ODBC datatypes are shown in lowercase letters, and similar ODBC datatypes are combined.

Datatype conversion for transfer to statements

Use the transfer to statement when you want to move data to a secondary database from the primary database.

The access service performs datatype conversions for bulk copy transfer to statements. These conversions are defined within the access service and are not affected by the settings of the datatype conversions configuration properties.

Datatype conversion takes place in two steps. The following table shows how incoming target ODBC datatypes are initially converted to Open Server datatypes.

Table 10-3 shows how the converted datatypes from the previous table are converted into actual datatypes of the target columns. If a column match is incompatible, the transfer ends with an error.

To fit the items in the table, the CS_ prefix is removed from the datatypes, datatypes are shown in lowercase letters, and similar Open Server datatypes are combined.

Processing Bulk Copy values

The following guidelines apply to bulk copy values.

Character datatypes

Character datatypes (CHAR, VARCHAR, TEXT) can be converted to any other datatype. Conversely, every datatype can be converted to character data. You must ensure that the character string is convertible to the target datatype.

For example, the character string "450" can be converted to a numeric datatype such as INTEGER or DECIMAL, but the character string "Hello" causes a value error going to a numeric datatype.

Numeric datatypes

Numeric datatypes can be converted to other numeric datatypes or to character datatypes, but they cannot be converted to binary or date datatypes.

Additional guidelines are as follows:

• All numeric conversions use rounding.

• Any loss of digits to the left of the decimal results in an error. For example, an integer of value 123 cannot be converted to a decimal (4,2) value without losing a digit to the left of the decimal point.

• Any loss of digits to the right of the decimal point is not considered an error. For example, a float of value 123.456 is converted to an integer of value 123 without error.

• When you transfer data from a decimal column to a float column, the precision of the result is not better than the precision of the target column.

  For example, if you transfer data from a decimal(15,0) column to a float column, then back to a decimal(15,0) column, the results in the target decimal(15,0) column do not match the results of the source decimal(15,0) column, due to the float column precision.

Date datatypes

Date datatypes can be converted to other date datatypes or to character strings. However, they cannot be converted to numeric or binary datatypes.

Binary datatypes

Binary datatypes can be converted to other binary datatypes or to character strings. However, these datatypes cannot be converted to numeric or other date datatypes.

Bulk Copy Transfer errors

Value errors occur during transfer processing when the value being inserted is out of range for the column datatype. The access service handles these errors using the following properties:

• CharConvertError

• NumConvertError

• DateTimeConvertError

• DefaultDate

• DefaultTime

If the SendWarningMessages property is set to yes, the access service sends a message to the client application when it encounters value errors.

Bulk copy value processing rules

The following rules apply for values during the bulk copy transfer process:

• NULL Values

  If a source column contains NULL values but the destination does not allow them, the row is rejected.

• Numeric Data

  • All numeric conversions use rounding.

  • Any loss of digits to the left of the decimal results in an error. For example, an integer of value 123 could not be converted to a decimal(4,2) value without losing a digit to the left of the decimal point.

  • Any loss of digits to the right of the decimal point is not considered an error. For example, a float of value 123.456 would be converted to an integer value of 123 without error.

• Binary Data

  Binary data is transferred to a binary column without byte translation. A byte value in the source will have the same value in the destination.

Values that cause errors

The following table shows data values that can cause errors during transfer.

Bulk copy Transfer error reporting

You can obtain bulk copy transfer error information in the following ways:

• If you include with report in the transfer statement, you receive a result set containing one VARCHAR column and one row indicating the number of rows transferred, rejected, and modified during processing.

• You can execute @@RejectedRowCount or @@DefaultedRowCount immediately after a successful transfer. These global variables return the number of rejected or defaulted rows.

• If you set SendWarningMessages to yes, the access service returns data conversion errors to the client application.