Teradata Cookbook

How to do it...

1. Create a source table with the following definition and containing a million rows:

CREATE volatile TABLE MergingTable_Source (ID DECIMAL(18,0),END_DT DATE,SOLD_AMT int)UNIQUE PRIMARY INDEX (ID) on commit preserve rows;

2. Create a target table with the following DDL:

CREATE volatile TABLE MergingTable_Target ( ID DECIMAL(18,0),END_DT DATE,SOLD_AMT int)UNIQUE PRIMARY INDEX (ID);

3. We will insert in the following MergingTable_Target table if the ID from MergingTable_Source does not exist. And if it does, we will update the END_DT and SOLD_AMT columns:

MERGE INTO MergingTable_Target tUSING MergingTable_Source       sON T.ID = S.IDWHEN MATCHED THENUPDATE SET END_DT = S.END_DT ,SOLD_AMT= S.SOLD_AMTWHEN NOT MATCHED THENINSERT (S.ID, ...