Archives of the TeradataForum
Message Posted: Thu, 01 Jul 2004 @ 15:20:17 GMT
This is a topic that comes up fairly often. I found a solution the other day that seems fairly simple and I don't think that it has been mentioned. It involves the use of the ROW_NUMBER() function.
Basically I built two tables a Staging table and the final one.
The staging table (call it T_STAG_DIM_DEFN) would have one column, DIM_CD (could have all of the keys) with an UPI on that code.
The final table (call it T_DIM_DEFN) has the DIM_CD and DIM_ID. I used VARCHAR for DIM_CD and INTEGER for DIM_ID.
Load the data into T_STAG_DIM_DEFN and since T_DIM_DEFN may or may not be empty, Row NUMBER runs from 1 to ... I need the current maximum value in the table:
SELECT (ROW_NUMBER() OVER (ORDER BY DIM_CD)) + (COALESCE(MAXDIM_ID,0)), DIM_CD, DIM_NM FROM T_STAG_DIM_DEFN, (SELECT MAX(DIM_ID) FROM T_DIM_DEFN) maxdim(MAXDIM_ID) WHERE DIM_CD NOT IN (SELECT DIM_CD FROM T_DIM_DEFN)
Since you have a write lock on the table, there are no semaphore issues. and it should run in parallel.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 23 Jun 2019|