LOAD TABLE Statement
Creates a new SPD Server table from an existing
SPD Server table by using a SELECT clause.
| Valid in: |
SPD Server |
| Explicit SQL pass-through facility |
| Requirements: |
When data is loaded between domains, the source and
destination domains must have the same backup setting. That is, both
domains must have BACKUP=YES or BACKUP=NO in their definition. When
domains have different backup settings, you must use CREATE TABLE
AS to create a table from an existing server table.
|
| The LOAD 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 LOAD TABLE statement
for use with tables in a Hadoop domain.
|
Syntax
LOAD TABLE new-table-name [
WITH index-name ON
(column-name) [ ','
WITH index-name ON
(column-name)]]
AS
SELECT select-list FROM old-table-name
[WHERE sql-expression]';'
Required Arguments
new-table-name
specifies the name
of the new server table.
old-table-name
specifies the name
of the source server table.
select-list
defines the columns
for the new table. Valid values are one or more column names or an
* (asterisk), which indicates all columns from the table. All characteristics
of the columns in the SELECT list are preserved and become permanent
attributes of the new table's column definitions.
Optional Arguments
WHERE sql-expression
specifies an sql-expression
that selects a subset of the data from the old table for the new table.
WITH index-name ON
(column-name) [ ',' WITH index-name ON
(column-name)]
creates indexes on
one or more columns in the new table.
Details
Use the LOAD TABLE statement to create a table from an existing table with one or
more indexes using a single statement. The SELECT statement enables you to use a subset
of the columns from the source table in the new table. The WHERE statement enables
you to subset the data. In general, the LOAD TABLE statement is faster than a corresponding
CREATE TABLE and CREATE INDEX statement pair, because it builds the table and one
or more associated indexes asynchronously by using
parallel processing.
Comparisons
Use LOAD TABLE when you want to create a new table that contains a subset of the columns
or data from an existing
SPD Server table. Use COPY TABLE when you want to duplicate the source table in its entirety.
Examples
Example 1: Create a New Table with Multiple Indexes
This example creates a server table named CarLoad that contains a subset of the data
from an SPD Server table named Cars. The creation of table CarLoad and its indexes
occurs in parallel.
execute(
load table carload with
index origin
on (origin),
index mpg
on (mpg)
as select *
from cars)
by sasspds;
Example 2: Creating Multiple Server Tables from a Single Server Table
In this example, multiple
EXECUTE statements are issued to create a table for individual U.S.
states from a global table called State that contains many states.
The first EXECUTE statement uses LOAD TABLE to create table State_AL
(Alabama), and creates an index on the County column. The structure
of the table State_AL and the data in the table both come from the
global table State. The data in State_AL is the subset of all records
from the State table in which the column value equals 'AL'. The LOAD
TABLE statement creates a table for all states (Alabama through Wyoming).
The table for each state is indexed by county and mirrors the structure
of the parent table State.
execute(load table state_al
with index county
on (county) as
select *
from state
where state='AL')
by sasspds;
execute(load table state_az
with index county
on (county) as
select *
from state
where state='AZ')
by sasspds;
...
execute(load table state_wy
with index county
on (county) as
select *from state
where state='WY')
by sasspds;
Copyright © SAS Institute Inc. All Rights Reserved.
Last updated: February 8, 2017