Sample Incremental Transfer

Create the spt_TableTransfer table, which stores transfer history:

sp_setup_table_transfer

Configure max transfer history. The default is 10, which means that SAP ASE retains 10 successful and 10 unsuccessful transfers for each table marked for incremental transfer. This example changes the value of max transfer history from 10 to 5:

sp_configure 'max transfer history', 5

Parameter Name               Default     Memory Used
	Config Value    Run Value              Unit
	Type                      Instance Name                  
-------------------------     -------     -----------
--------------   --------------------   -------
---------------          ---------------
max transfer history      10              0
5                5                        bytes               
dynamic                  NULL                           

Create the transfer_example table, which has the transfer attribute enabled and uses datarow locking:

create table transfer_example (
f1 int,
f2 varchar(30),
f3 bigdatetime,
primary key (f1)
) lock datarows
   with transfer table on

Populate the transfer_example table with sample data:

set nocount on
declare @i int, @vc varchar(1024), @bdt bigdatetime
select @i = 1
while @i <= 10
begin
   select @vc = replicate(char(64 + @i), 3 * @i)
   select @bdt = current_bigdatetime()
   insert into transfer_example values ( @i, @vc, @bdt )
   select @i = @i + 1
end
set nocount off

The script produces this data:

select * from transfer_example
order by f1

f1       f2                             f3
--------- ------------------------------ ----------------------------
   1      AAA                            Jul 17 2009  4:40:14.465789PM 
   2      BBBBBB                         Jul 17 2009  4:40:14.488003PM 
   3      CCCCCCCCC                      Jul 17 2009  4:40:14.511749PM 
   4      DDDDDDDDDDDD                   Jul 17 2009  4:40:14.536653PM 
   5      EEEEEEEEEEEEEEE                Jul 17 2009  4:40:14.559480PM 
   6      FFFFFFFFFFFFFFFFFF             Jul 17 2009  4:40:14.583400PM 
   7      GGGGGGGGGGGGGGGGGGGGG          Jul 17 2009  4:40:14.607196PM 
   8      HHHHHHHHHHHHHHHHHHHHHHHH       Jul 17 2009 4:40:14.632152PM 
   9      IIIIIIIIIIIIIIIIIIIIIIIIIII   Jul 17 2009 4:40:14.655184PM 
  10      JJJJJJJJJJJJJJJJJJJJJJJJJJJJJJ  Jul 17 2009 4:40:14.678938PM

Transfer the transfer_example data to an external file using the for ase format:

transfer table transfer_example
to 'transfer_example-data.ase'
for ase

(10 rows affected)

The data transfer creates this history record in spt_TableTransfer:

select id, sequence_id, end_code, ts_floor, ts_ceiling, row_count
from spt_TableTransfer
where id = object_id('transfer_example')

id        sequence_id      end_code     ts_floor       ts_ceiling 
row_count            
---------   -----------       --------      -------------   -------------------
592002109  1                0            0              5309	                    10

Disable the transfer attribute from transfer_example to demonstrate that the receiving table does not need the transfer attribute enabled to receive incremental data (the database must have select into enabled before you can run alter table):

alter table transfer_example
set transfer table off

After the alter table command runs, spt_TableTransfer is empty:

select id, sequence_id, end_code, ts_floor, ts_ceiling, row_count
from spt_TableTransfer
where id = object_id('transfer_example')

id        sequence_id      end_code     ts_floor       ts_ceiling 
row_count            
---------   -----------       --------      -------------   -------------------

(0 rows affected

Update transfer_example to set its character data to no data and to specify a date and time in its bigdatetime column so you can verify the table does not contain the original data.:

update transfer_example
set f2 = 'no data',
f3 = 'Jan 1, 1900 12:00:00.000001AM'

(10 rows affected)

After the update, transfer_example contains this data.

select * from transfer_example
order by f1

f1         f2                              f3                            
----------- ------------------------------ ---------------------------
1           no data                        Jan  1 1900 12:00:00.000001AM
2           no data                        Jan  1 1900 12:00:00.000001AM 
3           no data                        Jan  1 1900 12:00:00.000001AM 
4           no data                        Jan  1 1900 12:00:00.000001AM 
5           no data                        Jan  1 1900 12:00:00.000001AM 
6           no data                        Jan  1 1900 12:00:00.000001AM 
7           no data                        Jan  1 1900 12:00:00.000001AM 
8           no data                        Jan  1 1900 12:00:00.000001AM 
9           no data                        Jan  1 1900 12:00:00.000001AM 
10          no data                        Jan  1 1900 12:00:00.000001AM 

(10 rows affected)

Transfer the example data from the external file into transfer_example. Even though transfer_example is no longer marked for incremental transfer, you can transfer data in to the table. Because it has a unique primary index, the incoming rows replace the existing data and do not create duplicate key errors:

transfer table transfer_example
from 'transfer_example-data.ase'
for ase

(10 rows affected)

Select all data from transfer_example to verify that the incoming data replaced the changed data. The transfer replaced the contents of transfer_example.f2 and transfer_example.f3 tables with the data originally created for them, which were stored in the transfer_example-data.ase output file.

select * from transfer_example
order by f1

f1      f2                             f3                            
------- ------------------------------ ----------------------------
   1    AAA                            Jul 17 2009 4:40:14.465789PM 
   2    BBBBBB                         Jul 17 2009 4:40:14.488003PM 
   3    CCCCCCCCC                      Jul 17 2009 4:40:14.511749PM 
   4    DDDDDDDDDDDD                   Jul 17 2009 4:40:14.536653PM 
   5    EEEEEEEEEEEEEEE                Jul 17 2009 4:40:14.559480PM 
   6    FFFFFFFFFFFFFFFFFF             Jul 17 2009 4:40:14.583400PM 
   7    GGGGGGGGGGGGGGGGGGGGG          Jul 17 2009 4:40:14.607196PM 
   8    HHHHHHHHHHHHHHHHHHHHHHHH       Jul 17 2009 4:40:14.632152PM 
   9    IIIIIIIIIIIIIIIIIIIIIIIIIII    Jul 17 2009 4:40:14.655184PM 
  10    JJJJJJJJJJJJJJJJJJJJJJJJJJJJJJ  Jul 17 2009 4:40:14.678938PM 

Reenable transfer for transfer_example so subsequent transfers use, by default, the previous parameters:

alter table transfer_example
set transfer table on

(10 rows affected)