Performing MERGE INTO

MERGE statements can be used as an alternative to update statements. Updating rows is a CPU and I/O intensive operation, and, if you are updating PI on a big table which does not have a partition column, then updating columns becomes a challenge.

Getting ready

To complete this recipe you need to connect to the Teradata database and open SQLA.

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 t
USING MergingTable_Source       s...