Difference between revisions of "COPY"

Latest revision as of 15:31, 23 June 2010

Purpose

Copy all or part of the active table to another table or file

Syntax

COPY TO <filename> | (<expC1>)

[<scope>]

[DECRYPT | ENCRYPT <expC2>]

[FIELDS <field list>]

[FOR <condition>]

[WHILE <condition>]

[[WITH] CDX | [WITH] PRODUCTION]]

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

See Also

APPEND FROM, COPY FILE, COPY STRUCTURE, COPY STRUCTURE EXTENDED, CREATE BRIDGE, DECRYPT, ENCRYPT, JOIN, SET FILTER

Description

The COPY command copies records from the active table to another table or file. The file name can be substituted with a <expC1> enclosed in round brackets that returns a valid filename. If there is a table dictionary or a memo file on the current table, these files will also be copied to the new Recital table. If the current table is indexed, records will be copied in the indexed order, but the index file itself is not copied to the new table. Format files associated with the current table will not be copied to the new table. If SET FILTER TO is in effect, then only records that satisfy the filter condition are copied. If SET DELETED is ON, then records marked for deletion will not be copied.

To <filename>

The TO file will be created or, if it already exists, will be overwritten. If no file extension is specified for the TO <filename>, tables will default to '.dbf', XML files will default to '.xml', and all other files will default to '.txt'. The <filename> can include an 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>. This allows the creation of an encrypted copy of a non-encrypted table or an encrypted copy with a different key to the encrypted source table.

<scope>

If no <scope> is specified, then the default is ALL.

DECRYPT | ENCRYPT <expC2>

The DECRYPT and ENCRYPT clauses can be used to specify whether the target table of a COPY TO operation is encrypted or not. Specifying DECRYPT allows the creation of a non-encrypted copy of an encrypted table.

The ENCRYPT <expC2> clause encrypts the target table using the three part key specified in <expC2>. The <expC2> must contain a three part comma-separated key, each part a maximum of 8 characters, e.g. "key_1,key_2,key_3". Angled brackets may optionally enclose the key, e.g. "<key_1,key_2,key_3>". A .dkf file is created with the same basename as the target table. This allows the creation of an encrypted copy of a non-encrypted table or an encrypted copy with a different key to the encrypted source table. By default, when copying an encrypted table, if the key is not included in the <filename> and neither clause is specified, the target table is encrypted and has the same encryption key as the source table. A .dkf file is created with the same basename as the target table. If the source table is not encrypted and neither clause is specified, the target table will not be encrypted.

FIELDS <field list>

If the FIELDS clause is specified, then only those fields specified will be copied, otherwise all fields will be copied. The <field list> is a comma-separated list of field names. The fields specified can contain alias pointers, allowing copy to retrieve fields from multiple data files to be copied to a single data file. The number of records copied when using alias pointers is determined by the number of records in the table in the workarea from which the copy was initiated.

FOR <condition>

If the FOR clause is specified, then only those records which satisfy the specified <condition> are copied. The record pointer will always be positioned at EOF at the end of the operation if SET COMPATIBLE is in effect.

WHILE <condition>

If the WHILE option is used, the <scope> defaults to REST. The WHILE clause will copy records so long as the <condition> is true (.T.), and is used to restrict the range of records processed. When used in conjunction with the SEEK or LOCATE commands, it allows a quick way of copying selected records. The record pointer will always be positioned at EOF at the end of the operation if SET COMPATIBLE is in effect.

WITH CDX | WITH PRODUCTION

The [[WITH] CDX | [WITH] PRODUCTION]] clause causes the currently active multiple index file to be copied along with the table to a .cdx/.dbx file with the same basename as the TO <filename> when the target is a database table.

TYPE FIXED

If the target file type specified is FIXED, then the file will be created containing fixed length records without any record terminating character. This file type is useful for exporting records into a file that can be read by PASCAL, C, FORTRAN, etc.

TYPE SDF

If the target file type specified is SDF then the file will be created containing records as lines of text terminated with a carriage return/linefeed sequence. On UNIX the carriage return is not present. On OpenVMS the records are stored as variable length text records. If any of the fields being copied are binary fields, then the records are created in FIXED format. The maximum length of the text line used with COPY...SDF is 8192 characters.

TYPE XML

The XML clause copies the records to an Extensible Markup Language (XML) file. It also creates a matching Document Type Definition file with a '.dtd' file extension if the XML format is set to RECITAL. The default XML file format is Microsoft® ActiveX® Data Objects (ADO). This default can be set with the command SET XMLFORMAT TO <RECITAL | ADO>.

TYPE CSV

If the CSV clause is specified, then the target file will be created as a text file. Each field will be separated by a comma (,). Any occurrences of " embedded in character or memo fields will be escaped as "".

TYPE DELIMITED

If the DELIMITED clause is specified, then the target file will be created as a text file. Each field will be separated by a comma (,), and character fields will be enclosed in double quotes. If DELIMITED WITH BLANK is specified, then fields will be separated by a single space character instead of a comma. Character fields will not be enclosed. If DELIMITED WITH <delimiter> is specified, then the double quotes used to enclose character fields will be substituted with the <delimiter> and fields will be comma separated. If DELIMITED WITH TAB is specified, then fields will be separated by a tab character instead of a comma. Character fields will not be enclosed. Files created with the COPY TO...DELIMITED command can be appended into other Recital tables using the APPEND FROM...DELIMITED command.

Example

use patrons index names
copy to ballet for event = "BALLET"
seek "OPERA"
copy to opera rest;
  while event = "OPERA";
  for date = date()
 
// Another example
use payroll
// Copy to a file with today's name
copy to (cdow(date())) for amount > 100
// Make an encrypted copy
copy to encver<key_1,key_2,key_3>

Products

Recital Server, Recital