Archives of the TeradataForum

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

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

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>>