This task is associated with the Replace Load style. Based on the
type of target table that is being loaded, two or three of the following
selections are listed in the
Replace field:
-
Replace Entire table: uses PROC DATASETS to delete the target table
-
Replace All rows using
truncate: uses PROC SQL with TRUNCATE to remove all rows
(only available for DBMS tables that support truncation)
-
Replace All rows using
delete: uses PROC SQL with DELETE * to remove all rows
-
Replace Simulating
truncate: uses the DATA step with SET and STOP statements
to remove all rows (available only for SAS tables)
When you select
Replace Entire table, the table is removed and disk
space is freed. Then the table is recreated with
0
rows. Consider this option unless your security requirements restrict
table deletion permissions (a restriction that is commonly imposed
by a database administrator on database tables). Also, avoid this
method if the table has any indexes or constraints that SAS Data Integration
Studio cannot recreate from metadata (for example, check constraints).
If available, consider
using
Replace All rows using truncate. Either
of the replace all rows selections enables you to keep all indexes
and constraints intact during the load. By design, using TRUNCATE
is the quickest way to remove all rows. In
Replace All
rows using delete, the DELETE * syntax also removes all
rows. However, based on the database and table settings, this choice
can incur overhead that can degrade performance. Consult your database
administrator or the database documentation for a comparison of the
two techniques.
CAUTION:
When DELETE
* is used repeatedly to clear a SAS table, the size of that table
should be monitored over time. DELETE * performs only logical deletes
for SAS tables. Therefore, the table's physical size continues to
increase, which can negatively affect performance.
Replace
Simulating truncate is available for SAS tables. It does
not remove rows from a table as
Replace All rows using
delete does, or as
Replace All rows using
truncate does for a DBMS. It actually behaves more like
Replace Entire table in that the entire table is replaced
with an empty table before being loaded. Unlike
Replace
All rows using delete, this replace style does not have
the issue of ever-increasing table size.
Compared to
Replace Entire table,
Replace Simulating
truncate offers an advantage in that it can maintain
constraints such as check constraints that cannot be defined in metadata
for SAS Data Integration Studio. If a target table is to have check
constraints, the physical table must be created with all constraints
applied before a Table Loader can load it with
Replace
Simulating truncate. This can be done once, outside
of SAS Data Integration Studio or in user-written code in a SAS Data
Integration Studio job. When the Loader step runs and the target
table already exists, the step simulates a Truncate by creating an
empty table with structure and constraints that are identical to the
original, and then appends or inserts the data from the source table.
It is important to understand
that
Replace Simulating truncate, by design,
ignores all constraint metadata when code is generated (except to
create code to initialize the target if it does not already exist).
Therefore, constraints on the physical table cannot be modified by
changing constraint metadata and regenerated and rerunning with
Replace Simulating truncate.
Note: If you are using Generation
Data Sets, use the
Simulating Truncate load
technique instead of the DELETE * syntax.