All Forums Database
dev_teradata 4 posts Joined 04/13
25 Mar 2014
How to create auto generated ID in volatile table?

How to create auto generated ID in volatile table?

Raja_KT 1246 posts Joined 07/09
25 Mar 2014

you can tweak a select query with row_number
or you can create
CREATE CREATE MULTISET VOLATILE TABLE tmp (
      Field1 INTEGER GENERATED BY DEFAULT AS IDENTITY
         (START WITH 1
          INCREMENT BY 1
          MAXVALUE 100000000
         ),
      f2 INTEGER);
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dev_teradata 4 posts Joined 04/13
25 Mar 2014

This query is giving this error .CREATE TABLE Failed. 5784:  Illegal usage of Identity Column Field1.

dev_teradata 4 posts Joined 04/13
25 Mar 2014

I am inserting multiple values form different queries in volatile table so can't use row_number function.

Raja_KT 1246 posts Joined 07/09
26 Mar 2014

Hi,
From this link 
http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/index.html#page/General_Reference/B035_1096_111A/Database.26.1905.html it shows that it cannot be done.
 Probably,you can find out a way to generate a sequence from any of the queries having unique rows or you can populate or use/ from another/ independent permanent  table as temp and drop it later.
Cheers,
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Adeel Chaudhry 773 posts Joined 04/08
09 Apr 2014

You can use the last 3 posts at the following link:
 
http://forums.teradata.com/forum/database/generate-surrogatekey-with-a-huge-table

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.