You can always let a SAS Data Integration Studio transformation perform a simple load
of its output table that drops and replaces the table. However, you can also add a
Table Loader transformation to a permanent output table. Then, you can use the options
in the Table Load transformation to control how data is loaded into the
target table. In fact, a separate Table Loader transformation might be desirable under the
following conditions:
-
loading a DBMS table with any technique
other than drop and replace.
-
loading tables that contain rows that must be updated upon load (instead of dropping
and recreating the table each time the
job is executed).
-
creating primary keys, foreign
keys, or column constraints.
-
performing operations on constraints
before or after the loading of the output table.
-
performing operations on indexes
other than after the loading of the output table.
-
The Table Loader transformation generates code that reads a single
source table (or view) and updates, replaces, or appends it to a permanent target table.
Supported target types include SAS, Excel, and a wide variety of DBMS types.
For data types that support constraints such as not-null and primary, unique, and
foreign keys, a Table Loader can be set to generate the appropriate code to add or
remove constraints. Constraint actions can be set independently for before and after
the load. Likewise, the adding and removing of indexes can be controlled in the same
way.
Choosing the Load Style and Technique is critical to getting the Table Loader to perform
the correct task for the job efficiently. User requirements control which style (Update,
Replace, or Append) to
select. Once the style has been selected, a number of possible techniques to accomplish
the task are presented. Choosing the correct technique is often a matter of deciding
which technique will likely result in the best performance for the job when it later
runs in production. The exact number and types of available styles and techniques
depend on the target’s data type. Some data types support clearing old rows by using
a technique known
as Truncate, while others do not. Some data types support a special Upsert technique,
which updates rows that match on a specific key and appends the other rows to the
master. Some support direct access; for those, the DATA step Modify technique is a
choice. For more information about all the available techniques, see the Help topic
for the Load Technique.
Once the
technique is chosen, additional options that are associated with the
selected technique should be reviewed to determine whether any option
values should be changed from their defaults. Also, with performance
in mind, you should consider any special handling of constraints and
indexes.
It is
important to know that non-loader transformations can load data directly
into a permanent table if it has no constraints, in effect doing a Replace Entire table without using a Table Loader. This
is done in the Job Editor by replacing the
non-loader’s output WORK table with a registered table. This
technique is not supported by all transformations for all data types.
A new Replace Simulating truncate load style has been added for SAS targets. This choice empties the output table by
using a DATA step with SET and STOP statements. This actually recreates the target
table with no rows before data from the source is appended. Original data is physically
deleted, not just logically deleted as with
Replace All rows using delete. Constraints are restored
as they were on the physical table before the load.
CAUTION:
When using this load style, the new table structure
is derived from the physical table (assuming it pre-existed) and not
from metadata. This load style does not reflect changes to the column,
index, or constraint metadata after the creation of the table.
One feature
that is available for SAS tables with Replace Simulating
truncate, but not available with other Replace types, is the ability to use generation data
sets. Generation data sets are a way of automatically saving a specified number of
backups of the target. In SAS, this feature is enabled by adding the data set option
GENMAX=#.