Specifies which columns in the master table are used to generate a WHERE condition for a Teradata MultiLoad upsert.
| Valid in: | PROC APPEND (when accessing DBMS data using SAS/ACCESS software) |
|---|---|
| Categories: | Bulk Loading |
| Data Set Control | |
| Default: | none |
| Requirement: | MULTILOAD=YES must be specified. |
| Data source: | Teradata |
| See: | MULTILOAD= data set option, UPSERT= data set option, UPSERT_CONDITION= data set option |
Table of Contents
specifies one or more columns that are used to generate conditions for upsert processing of Teradata tables.
By default, TPT MultiLoad upsert uses only the primary index columns of the target table to generate the WHERE condition for upsert processing.
Upsert processing requires a WHERE clause on the primary index of the target table to identify the target rows to be updated. When you are using the upsert feature, a WHERE condition is built, by default, based on the primary index columns of the target table.
When you need additional columns to identify target rows, use UPSERT_WHERE= to list the columns to be used. Note that you need to include the columns that make up the primary index of the target table.
libname x teradata user=user pw=pw;
/* Create Mybase base table-Column i where i is the primary index */
data x.master;
i=1;
j=1;
k=1;
output;
i=1;
j=2;
k=2;
output;
run;
/* Create a transaction table */
data x.transaction;
i=1;
j=2;
k=99;
output;
run;
/* Running Upsert with UPSERT_WHERE=(i j) generates an update Where clause
based on columns i and j. All other columns in the master 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 "k"=:"k";
WHERE "i"=:"i" AND "j"=:"j";
INSERT "mybase"("i","j","k") VALUES (:"i",:"j",:"k");
*/
proc append base=x.mybase (MULTILOAD=YES UPSERT=YES UPSERT_WHERE=(i j))
data=x.transaction;
run;