Append From Array, Copy To Array

These commands let you move data between tables and arrays. APPEND FROM ARRAY adds data to a table from an array, while COPY TO ARRAY moves table data into an array.

Usage

APPEND FROM ARRAY ArrayName
        [ FIELDS FieldList 
         | LIKE IncludeList | EXCEPT ExcludeList ] 
        [ FOR lExpr ]
COPY TO ARRAY ArrayName
        [ FIELDS FieldList 
         | LIKE IncludeList | EXCEPT ExcludeList ] 
        [ Scope ] [ FOR lExpr1 ] [ WHILE lExpr2 ]
        [ NOOPTIMIZE ]

APPEND FROM ARRAY and COPY TO ARRAY use the table open in the current work area. With COPY TO, if the array doesn't already exist, it's created as a two-dimensional array.

Many folks don't realize that these commands behave differently depending on whether ArrayName is one-dimensional or two-dimensional. With a one-dimensional array, these commands operate on a single record of the current table. With a two-dimensional array, they operate on multiple records. This behavior often trips people up when they want to copy a single field from multiple records. They create a one-dimensional array with as many items as records to be copied, but when they COPY TO ARRAY, they get just one field. Instead, either make sure the array doesn't already exist (so it gets created) or dimension it as a two-dimensional array with a single column.

USE Employee
DIMENSION aCity[RECCOUNT(), 1]
COPY TO ARRAY aCity FIELDS City

The FIELDS clause lets you specify which field or fields are involved. With APPEND FROM ARRAY, when no field list is specified, data is copied from the array into fields, starting with the first, until either all fields get a value or all columns have been used. With COPY TO, you get one column of the array for each field of the table. When a field list is specified for either command, columns are matched to the fields in the order they're listed. Starting in FoxPro 2.6, you can give either a specific list of fields to include or you can provide skeletons for field names to include and exclude. Both include and exclude skeletons can be used, and they work together to generate the complete result. For example, to get all character information from a table that precedes character fields with "c", but omit phone numbers, use:

USE TheTable
COPY TO ARRAY aMyData FIELDS LIKE c* EXCEPT cPhone

Incredibly, you can use LIKE and EXCEPT with APPEND FROM ARRAY as well to copy array data into specific fields. What you can't do in either APPEND FROM ARRAY or COPY TO ARRAY is combine a list of specific fields with inclusion and exclusion. Too bad. It would be very cool to be able to write something like:

USE TheTable
COPY TO ARRAY aMyData FIELDS cFirstName, cLastName LIKE n*

and end up with the name and all numeric fields.The FOR clause of APPEND FROM ARRAY evaluates each row as if it had already been added to the table. If it doesn't pass the test, it's not added, however. This is the same way that other flavors of APPEND operate; it goes way back in Xbase history.The Scope, FOR and WHILE clauses of COPY TO ARRAY determine which records get copied to the array. See "Scope, FOR, WHILE and Santa Clauses" in "Xbase Xplained" for an explanation.You can probably ignore NOOPTIMIZE. See SET OPTIMIZE if you don't want to.Actually, you can probably ignore the whole command. We don't find much use for COPY TO ARRAY. SQL SELECT can do everything COPY TO ARRAY does and more. One exception: COPY TO ARRAY is better than SELECT ... INTO ARRAY when you're dealing with buffered data, because the SQL SELECT won't see any uncommitted changes, while COPY TO ARRAY will.

Example

* Copy everyone in Sales to array.
USE Employee
COPY TO ARRAY aSales FOR UPPER(Title) = "SALES"
 
* Now add them to some other table.
SELECT SalesStaff
APPEND FROM ARRAY aSales