APPEND FROM

Purpose

Append records to the active table from another table or external file

Syntax

APPEND FROM <filename> | (<expC1>)

[ENCRYPTION <expC2>]

[FOR <condition>]

[WHILE <condition>]

Description

The APPEND FROM command adds records from another file to the end of the active table. The FROM file can be in any one of the formats shown following the TYPE option. The <filename> can be substituted with a <expC1>, enclosed in round brackets, which returns a valid filename. The <filename> can include the encryption key for encrypted database tables. The three part comma-separated key should be enclosed in angled brackets and appended to the filename, e.g. mytable<key_1,key_2,key_3>.

If the FROM file is another Lianja table, it cannot be open and active at the time of the APPEND. When processing the FROM file, only fields which exist in both tables are copied. If the field in the FROM file is longer than the field in the active table it will be truncated. If a field in the FROM file is shorter than that in the active table, then the field in the active table is padded with blanks if it is a character field, otherwise it is converted to the new width. If no file extension is specified, then a '.dbf' extension is assumed. Records marked for deletion are not appended if SET DELETED is ON.

If the FROM file is a table, then the table and any associated index files will be locked while the records are being appended, to optimize the speed of the operation.

If the FROM file is not a table and the active table is shared, then the table and any associated index files will be locked as each record is appended. The locks are only applied as each record is added to the table, and not enforced for the full duration of the append operation, to provide optimum concurrent access to the table.

Any indexes currently associated with the table will be automatically updated as the new records are added.

If a FILTER <condition> is currently active, then only those records in the FROM file which satisfy the specified <condition> will be appended.

ENCRYPTION <expC2>

If the FROM table is encrypted, its DES3 encryption key must be entered correctly before the data can be accessed. The key can be specified using ENCRYPTION <expC2>, where <expC2> is the 3 part encryption key, e.g. "key_1,key_2,key_3". The SET ENCRYPTION command allows a default encryption key to be defined. If the ENCRYPTION <expC2> clause is not specified and the key is not included in the <filename>, this default key will be used. If the default key is not the correct key for the FROM table, an error will be given. If no default key is active, a dialog box will be displayed in character mode Lianja to allow the user to enter the key.

FOR <condition>

If the FOR <condition> clause is specified, then only those records in the FROM file which satisfy the specified <condition> will be appended.

WHILE <condition>

The WHILE <condition> clause can be used to restrict the range of records which are appended. When the <condition> becomes false, the APPEND operation will stop.

TYPE FIXED

If the FIXED keyword is specified, then the FROM file must contain fixed length records, where each field is exactly the same width as that in the active table. A FIXED file does not contain a deletion marker as the first character of each record. If no file extension is specified, then '.txt' is assumed. FIXED files can be created with the Lianja COPY...FIXED command, or they can be created by an external program written in another programming language (e.g. C, PASCAL, FORTRAN).

TYPE DELIMITED

If the DELIMITED option is used, each record ends with a carriage return/line feed. If no WITH has been specified in the DELIMITED option, then a ',' will separate each field, and character fields will be surrounded by "" double quotes. If no file extension is specified, then a '.txt' extension is assumed. DELIMITED files can be created using the Lianja COPY...DELIMITED command or they too can be created by an external program.

TYPE SDF

If the SDF keyword is specified, then records from a text file which end with a carriage return/line feed can be appended. The maximum length of the text line used with APPEND FROM...SDF is 8192 characters. If there are any binary fields in the FROM file, then the SDF file is treated as being in FIXED format.

TYPE CSV [MEMO]

IF the CSV keyword is specified, then records from an MS Excel CSV file can be appended. If SET CSVHEADING is ON (default), the first line of the CSV file is assumed to contain the field names and is not appended to the table. If SET CSVHEADING is OFF, the first line of the CSV file is treated as the first data record to be appended.

If the MEMO keyword is included then MEMO (Varchar) fields are supported (from v3.2).

Example

use patrons
append from system type sdf
append from textfile type delimited
append from transactions for code <> "D"
 
// Another Example
use payroll
append from (iif(dow(date())>5, "weekend.dbf","weekday.dbf") for amount > 100