COPY TO ARRAY example

The following example uses COPY TO ARRAY and APPEND FROM ARRAY to copy records between tables where the fields are the same data type, but may not have the same field names. (If the field names were the same, the APPEND FROM command would be easier.) To minimize disk access, records are read in blocks of 100.

PROCEDURE AppendByPosition( cSource )

#define BLOCK_SIZE 100 

local cTarget, aRec, nRecs, nCopied 

*-- Get alias for current table 

cTarget = alias( ) 

use ( cSource ) in select( ) alias SOURCE 

if reccount( "SOURCE" ) == 0 

*-- If source table is empty, do nothing 

return 0 

endif 

*-- Create array with default block size 

aRec = new Array( BLOCK_SIZE, FLDCOUNT( "SOURCE" ) ) 

nCopied = 0 

do while .not. eof( "SOURCE" ) 

*-- Calculate number of records to copy, the smaller of 

*-- the block size and the number of records left 

nRecs = min( BLOCK_SIZE, reccount( "SOURCE" ) - nCopied ) 

if nRecs < BLOCK_SIZE 

*-- Resize array for last block to copy 

aRec.resize( nRecs, FLDCOUNT( "SOURCE" ) ) 

endif 

select SOURCE 

*-- Copy next block 

copy to array aRec rest 

*-- Move from last record copied to first record in next block 

skip 

select ( cTarget ) 

append from array aRec 

nCopied = nCopied + nRecs 

enddo 

use in SOURCE 

return nCopied 

The COPY TO ARRAY command uses the REST scope to copy the next block of records. Because the number of records to copy is known (it’s calculated for the nRec variable), NEXT nRec would also work, but it’s redundant, because the array has been sized to copy the right number of records. The array sizing is important because that determines the number of records that get appended with APPEND FROM ARRAY.