Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 16 Mar 2006 @ 10:56:19 GMT


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


Subj:   Re: AUTO_NUMBER in Teradata
 
From:   Jim Tepin

This is coming off the top of my head, so I hope the syntax is proper.

I have similar needs and have almost always turned to manual generation. I requires that you data be staged so that you can generate ids for new records. You need to use an ordered analytical function.. this uses Rank(), but there are similar ways with SUM(). Note.. in this example, you would get an identical rank value if you happen to have more than one set of FIELD1,FIELD2.. so be sure your OVER statement references a set of logical key fields.

     INSERT INTO TARGETTABLE
     SELECT   MAXSEQ +
                      RANK()
                      OVER(ORDER BY FIELD1,FIELD2)  AS NEWSEQ
                  , SOMEFIELD
                  , ANOTHERFIELD
     FROM    STAGINGTABLE   STG
     CROSS JOIN
                   (SELECT MAX(SEQUENCE_ID) AS MAXSEQ
                    FROM  TARGETTABLE)  DT1


     
  <Prev Next>   <<First <Prev
Next>
Last>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023