Archives of the TeradataForum

Message Posted: Wed, 12 Apr 2006 @ 19:14:55 GMT

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

Ole / Hello:

The following algorithm:

- Can be used in UPSERT scenarios (generates surrogate keys just for the new records.

- Is fast.

- Leaves no 'holes'.

- Deals with duplicates in the input table.

     .....

     FROM    TabelaFonte  T0
     JOIN
         (
             SELECT  COALESCE(T2.SURROGATE_KEY,
                         SUM (CASE WHEN T2.SURROGATE_KEY IS NULL THEN 1 ELSE 0 END)
                         OVER( ORDER BY T1.CHAVE_SISTEMA_FONTE ROWS UNBOUNDED
     PRECEDING ) + TM.MAX_GERADA
                     ) AS SURROGATE_KEY,
                     T1.CHAVE_SISTEMA_FONTE
             FROM    ( SEL COALESCE( MAX(SURROGATE_KEY), -2147483648) AS MAX_GERADA
     FROM TabelaDestino ) TM
                     CROSS JOIN
                     ( SEL CHAVE_SISTEMA_FONTE FROM TabelaFonte GROUP BY
     CHAVE_SISTEMA_FONTE ) T1
                     LEFT OUTER JOIN
                     TabelaDestino  T2
             ON      T1.CHAVE_SISTEMA_FONTE = T2.CHAVE_SISTEMA_FONTE
         ) AS T2
     ON  T0.CHAVE_SISTEMA_FONTE = T2.CHAVE_SISTEMA_FONTE
     ;

     CHAVE_SISTEMA_FONTE = NATURAL KEY
     TabelaFonte = SOURCE TABLE
     TabelaDestino = TARGET TABLE

Cheers,

Cesar Ureta

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