transfer table

Initiates an incremental table transfer.

Syntax

transfer table [[db.]owner.]table [to | from] destination_file
	[ for { ase | bcp | iq | csv } ]
	[ with {column_separator=string}, {column_order=option},
	{encryption=option}, {row_separator=string},
	{resend=id}, {progress=sss}, {tracking_id=nnn} 
	{sync = true | false]}, {fixed_length = true | false}
		, null_byte = true | false}]

Parameters

Examples

Usage

  • transfer table sends only committed data that has changed since the previous transfer.

  • The string argument to column_separator and row_separator in the with clause may be up to 64 bytes long, and may contain formatting instructions:

    • “\b”indicates a backspace, <BS> (Ctrl+H).

    • “\n” indicates a newline, <LF> (Ctrl+J).

    • “\r” indicates a carriage return, <CR> (Ctrl+M).

    • “\t” indicates a <TAB> (Ctrl+I).

    • “\\” indicates a backslash.

    • Any “\” in the string that is not part of one of these sequences is an actual backslash, and appears as such in the string.

  • transfer table .. from does not fire triggers during updates or inserts.

  • When transfer table runs into an error (such as a duplicate key), the SAP ASE server displays just an error number, making it difficult to understand the cause of the error. For example:
    Msg 2633, Level 20, State 1
    Server 'SYB155', Line 1
    TRANSFER TABLE failed to insert a row to table 'my_tab'. 
    The indicated error was 2601.
    Msg 16025, Level 16, State 1
    Server 'SYB155', Line 1
    TRANSFER TABLE my_tab: command failed with status 2633.
    To retrieve the error message, manually query master..sysmessages. For example, if 2601 is your error number, enter:
    select * from master..sysmessages where error = 2601
    See the Troubleshooting Guide for more information about error 2601.
You can use transfer table for tables that are not marked for incremental transfer, with these restrictions:
  • Not all rows are always transferred. If a user updates the table while the transfer is in progress, the updated rows may not be transferred.

  • The transfer is not incremental; you can transfer only the entire table, and later transfers are not notified of this transfer.

  • No history entry is written to spt_TableTransfer. The transfer appears in monTableTransfer for the duration of the transfer, but once the transfer is complete the record vanishes.

Permissions

Permission to transfer a table does not automatically grant permission to decrypt data within that table. To decrypt any encrypted columns, you must gain specific permission from the table owner.

The following describes permission checks for transfer table that differ based on your granular permissions settings.

SettingDescription
Enabled

With granular permissions enabled, you must be the table owner or a user with tranfer table permission on the table.

Disabled

With granular permissions disabled, you must be the table owner, be a user with transfer table permission, or a user with sa_role.

Auditing

Values in event and extrainfo columns of sysaudits are:

InformationValues
Event

136

Audit option

transfer table

Command or access audited

transfer table

Information in extrainfo
Related reference
alter table
create table