COPY

How to:

Reference:

The COPY command copies some or all of the rows of one stack into another stack. You can use the COPY command to overwrite existing rows in the target stack, to add new rows, or to create the entire target stack.

You must define the contents of a stack before copying data into it. This can be accomplished by issuing a NEXT or an INFER command for data source fields, and COMPUTE for non-data source fields.

Source and target database stacks used in the COPY command must be derived from the same data source description. The COPY command checks that the data source and segment names are the same, and copies all columns in the source stack whose names and data types exactly match columns in the target stack. In this context, data type refers to the basic data type (such as integer) and all other data attributes including length, precision, null (MISSING), and display options such as zero (0) suppression. Source and target columns do not need to be in the same sequence.

Syntax: How to Use the COPY Command

The syntax of the COPY command is 

[FOR {int|ALL}|STACK] COPY FROM {stk[(row)]|CURRENT}
INTO {stk[(row)]|CURRENT} [WHERE expression] [;]

where:

FOR

Is a prefix used with int or ALL to specify the number of rows to copy from the source (FROM) stack into the target (INTO) stack. If you omit both FOR and STACK, only the first row of the source stack is copied.

int

Is an integer expression that specifies how many source stack rows to copy into the target stack. If int exceeds the number of source stack rows between the starting row and the end of the stack, all of those rows are copied.

ALL

Indicates that all of the rows starting with either the first row or the subscripted row are copied from the source (FROM) stack into the target (INTO) stack.

STACK

Is a synonym for the prefix FOR ALL. If you omit both FOR and STACK, only the first row of the source stack is copied.

FROM

Is used with a stack name to specify the stack from which to copy the data.

INTO

Is used with a stack name to specify the stack to be created or modified.

stk

Is the name of the source or target stack. You can specify the same stack as the source and target stacks.

row

Is a stack subscript that specifies a starting row number. It can be a constant, an integer variable, or any Maintain Data expression that results in an integer value. If you omit row, it defaults to 1.

CURRENT

Specifies the Current Area. If you specify CURRENT for the source stack, all Current Area fields that also exist in the target stack are copied to the target stack. You cannot specify CURRENT if you specify FOR or STACK.

WHERE

Specifies selection criteria for copying stack rows. If you specify a WHERE phrase, you must also specify a FOR or STACK phrase.

expression

Is any Maintain Data expression that resolves to a Boolean expression. Unlike an expression in the WHERE phrase of the NEXT command, it does not need to refer to a data source field.

;

Terminates the command. Although the semicolon is optional, it is recommended that you include it to allow for flexible syntax and better processing. For more information about the benefits of including the semicolon, see Terminating a Command's Syntax.

Example: Copying All Rows of a Stack

The following example copies the entire Emp stack into a new stack called Newemp: 

FOR ALL COPY FROM Emp INTO Newemp;

Example: Copying a Specified Number of Stack Rows

The following example copies 100 rows from the Emp stack starting with row number 101. The rows are inserted beginning with row one of the stack Subemp: 

FOR 100 COPY FROM Emp(101) INTO Subemp;

Example: Copying the First Row of a Stack

The following example copies the first row of the Emp stack into the first row in the Temp stack. Only the first row in the source (FROM) stack is copied because this is the default when a prefix is not specified for the COPY command. The data is copied into the first row of the Temp stack because the first row is the default when a row number is not supplied for the target (INTO) stack: 

COPY FROM Emp INTO Temp;

Example: Copying a Row Into the Current Area

The following example copies the tenth row of the Emp stack into the Current Area. Only one row is copied from the Emp stack because the COPY command does not have a prefix. Every column in the stack is copied into the Current Area. If there is already a field in the Current Area with the same name as a column in the stack, the Current Area variable is replaced with data from the Emp stack: 

COPY FROM Emp(10) INTO CURRENT;

Example: Copying Rows Based on Selection Criteria

You can also copy selected rows based on selection criteria. The following example copies every row in the World stack that has a Country equal to USA into a new stack called USA: 

FOR ALL COPY FROM World INTO USA WHERE Country EQ 'USA';

The following takes data from one stack and places it into three different stacks: one to add data, one to change data, and one to update data. 

FOR ALL COPY FROM Inputstk INTO Addstk WHERE Flag EQ 'A';
FOR ALL COPY FROM Inputstk INTO Delstk WHERE Flag EQ 'D';
FOR ALL COPY FROM Inputstk INTO Chngstk WHERE Flag EQ 'C';
FOR ALL INCLUDE Dbfield FROM Addstk;
FOR ALL DELETE Dbfield FROM Delstk;
FOR ALL UPDATE Dbfield1 Dbfield2 FROM Chngstk;

Example: Appending One Stack to Another

The following example takes an entire stack and adds it to the end of an existing stack. The subscript consists of an expression. Yeardata.FocCount is a stack variable where Yeardata is the name of the stack and FocCount contains the number of rows currently in the stack. By adding one to FocCount, the data is added after the last row: 

FOR ALL COPY FROM Junedata INTO Yeardata(Yeardata.FocCount+1);

Reference: Usage Notes for COPY

  • If the FOR int prefix specifies more rows than are in the source (FROM) stack, all of the rows are copied.
  • Only the first row of the source (FROM) stack is copied if the COPY command does not include FOR.
  • The entire stack is copied if the source (FROM) stack is not subscripted and FOR ALL is used.
  • The row to start copying from defaults to the first row unless the source (FROM) stack is subscripted. If the source (FROM) stack is subscripted, the copy process starts with the row number and copies as many rows as specified in the FOR n prefix, or the remainder of the stack if FOR ALL is specified.
  • No change is made to the source (FROM) stack unless it is also the target (INTO) stack.
  • INTO CURRENT cannot be used with the FOR phrase and generates an error if specified.
  • To copy an entire stack, specify FOR ALL without a subscripted source (FROM) stack.
  • Stack columns created using the COMPUTE command cannot be copied into the Current Area.
  • If the source (FROM) stack is the Current Area, the only Current Area fields that are copied are those that have a corresponding column name in the target (INTO) stack.
  • If the target (INTO) stack is not subscripted, the data is copied into the first row in the stack. If the target (INTO) stack is subscripted, the copied row or rows are inserted at this row.
  • If the COPY command specifies the command output destination as a row or rows of an existing stack that already have data in them, then the old data in these rows is overwritten with the new data when the COPY is executed.
  • If the source (FROM) stack has fewer columns than the target (INTO) stack, the columns that do not have any data are initialized to blank, zero (0) , or null (missing) as appropriate.
  • Source (FROM) stack rows will overwrite the specified target (INTO) stack rows if they already exist.
  • If the COPY command creates rows in the target (INTO) stack, and the target (INTO) stack contains columns that are not in the source (FROM) stack, those columns in the new rows will be initialized to their default values of blank, zero (0), or null (missing).
  • If the source (FROM) stack has more columns than the target (INTO) stack, only corresponding columns are copied.
  • The FOR prefix copies rows from the source (FROM) stack one row at a time, not all at the same time. For example, the following command:
    FOR ALL COPY FROM Car(Car.FocIndex) INTO Car(Car.FocIndex+1);

    copies the first row into the second, then copies those same values from the second row into the third, and keeps going. When the command has finished executing, all rows will have the same values as the first row.

Reference: Commands Related to COPY

  • INFER. Defines the columns in a stack.
  • COMPUTE. Defines the columns in a stack for non-data source fields.
  • NEXT. Defines the columns in a stack and places data into it.