COPY TABLE Statement

Copies an SPD Server table.

Valid in:	SPD Server
	Explicit SQL pass-through facility
Requirements:	When tables are copied between domains, the source and destination domains must have the same backup setting. That is, both domains must have either BACKUP=YES or BACKUP=NO in their definition. When domains have different backup settings, you must use PROC COPY to copy a table between the domains.
	The COPY TABLE statement requires local direct access to the source and destination tables from the machine that the server is running on. SPD Server does not support the COPY TABLE statement for use with tables in a Hadoop domain.

Syntax

Example 1: Copy a Table with and without Indexes

Example 2: Copy a Table and Order Its Columns

Syntax

COPY TABLE new-table-name FROM old-table-name [WITHOUT INDEXES]

[ORDER BY column-name [ ASC | DESC ] [',' column-name [ ASC | DESC ]]] ';'

Required Arguments

new-table-name

specifies the name of the new server table.

old-table-name

specifies the name of the source server table.

Optional Arguments

ORDER BY column-name [ASC |DESC][, column-name [ASC |DESC]]

optional: sorts the data in the new table by one or more columns, setting the data in the columns in ascending or descending order.

WITHOUT INDEXES

optional: suppresses creation of indexes.

Details

Use COPY TABLE to copy an existing SPD Server table from one server domain to another. You can copy the table with or without indexes. COPY TABLE offers the same parallel table and index I/O and overlapped input as the LOAD TABLE statement.

(Optional) You can optionally specify a new sort order.

Comparisons

Use COPY TABLE when you want to duplicate an SPD Server table in its entirety. Use LOAD TABLE when you want to create a new table that contains a subset of the columns or data from the source SPD Server table.

Examples

Example 1: Copy a Table with and without Indexes

The following example creates two new tables: T_NEW and T2_NEW. The first table, T_NEW, is created with index structures identical to table T_NEW. The second table, T2_NEW, is unindexed, regardless of the structure of table T2_OLD.

execute(copy table t_new
from t_old)
by sasspds;

execute(copy table t2_new
from t2_old
without indexes)
by sasspds;

Example 2: Copy a Table and Order Its Columns

COPY TABLE does not support all of the options of PROC SORT. However, you can achieve substantial performance gains when you create ordered tables by using the COPY TABLE statement with an ORDER BY clause when appropriate. This example copies the table T_OLD to T_NEW using the ORDER BY clause. The data is ordered by columns: X in ascending order, Y in descending order, and Z in ascending order. The results are the same if you run PROC SORT on the columns using the same BY clause. The syntax of the COPY TABLE ORDER BY follows the typical SQL ORDER BY clause, but the column identifiers that you can specify are restricted. You can specify only actual table columns when you use the COPY TABLE ORDER BY clause.

execute(copy table t_new
from t_old
order by x, y desc, z asc)
by sasspds;

Last updated: February 8, 2017