Difference between revisions of "APPEND FROM"

Revision as of 14:04, 10 November 2009

Purpose

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

Syntax

APPEND FROM <filename> | (<expC1>)

[ENCRYPTION <expC2>]

[FOR <condition>]

[WHILE <condition>]

[[TYPE] SDF | FIXED | DELIMITED | DELIMITED WITH BLANK | DELIMITED WITH <delimiter> | CSV]

See Also

APPEND, APPEND BLANK, APPEND FROM ARRAY, CREATE BRIDGE, COPY, DECRYPT, ENCRYPT, MERGE, SET DELETED, SET ENCRYPTION, SET EXCLUSIVE, USE

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 Recital table, it cannot be open and active at the time of the APPEND. When processing the FROM file, the Recital/4GL only copies fields which exist in both of the tables. 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 Recital Terminal Developer 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 Recital/4GL 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 Recital/4GL 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. On UNIX, the carriage return is not present. On OpenVMS the records are variable length text records. If there are any binary fields in the FROM file, then the SDF file is treated as being in FIXED format.

TYPE CSV

IF the CSV keyword is specified, then records from an MS Excel CSV file can be appended.

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

Products

Recital Server, Recital