Specifies whether TPT MultiLoad upsert should take place.
| Valid in: | PROC APPEND (when accessing DBMS data using SAS/ACCESS software) |
|---|---|
| Category: | Bulk Loading |
| Default: | NO |
| Requirement: | MULTILOAD=YES must be specified. |
| Data source: | Teradata |
| See: | MULTILOAD= data set option, UPSERT_CONDITION= data set option, UPSERT_WHERE= data set option |
Table of Contents
uses the TPT MultiLoad upsert feature.
performs inserts without upserts.
The TPT MultiLoad bulk-load facility supports the Teradata upsert feature. When an update fails because a target row does not exist, the upsert feature updates the table by inserting the missing row.
The upsert feature performs a combination of updates and inserts in one step. When updating a base table with a transaction table, an UPDATE statement is first issued on the base table using a row of data from the transaction table. If no target row exists to satisfy the update, an INSERT statement adds the transaction row to the base table.
Upsert requires a WHERE clause on the primary index of the target table to identify the target rows to be updated. When UPSERT=YES, Teradata builds a WHERE condition, by default, based on the primary index columns of the target table.
To add conditions to the WHERE condition, use UPSERT_CONDITION=.
libname x teradata user=user pw=pw;
/* Create the Mybase base table-Column where i is the primary index */
data x.mybase;
i=1;
j=1;
output;
i=2;
j=2;
output;
run;
/* Create a transaction table */
data x.transaction;
i=1;
j=99;
output;
run;
/* Running Upsert operates an Update statement based on the primary index
column i. All other columns in the base are updated with transaction data.
A snippet of the generated MultiLoad script is included:
.DML Label SASDML DO INSERT FOR MISSING UPDATE ROWS;
UPDATE "mybase"
SET "j"=:"j";
WHERE "i"=:"i";
INSERT "mybase"("i","j") VALUES (:"i",:"j");
*/
proc append base=x.mybase (MULTILOAD=YES UPSERT=YES) data=x.transaction;
run;