Archives of the TeradataForum

Message Posted: Thu, 01 Jul 2004 @ 15:20:17 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

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:

Then run

     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.

Glen

	<Prev	Next>		<<First	<Prev	Next>	Last>>