Add a sequential number on create / insert - Teradata

Question

In oracle we would use rownum on the select as we created this table. Now in teradata, I can't seem to get it to work. There isn't a column that I can sort on and have unique values (lots of duplication) unless I use 3 columns together.

The old way would be something like,

create table temp1 as 
  select
    rownum as insert_num,
    col1,
    col2,
    col3
  from tables a join b on a.id=b.id
;

Solution 1

This is how you can do it:

create table temp1 as 
( 
   select
      sum(1) over( rows unbounded preceding ) insert_num
     ,col1
     ,col2
     ,col3
   from a join b on a.id=b.id
) with data ;

Solution 2

Teradata has a concept of identity columns on their tables beginning around V2R6.x. These columns differ from Oracle's sequence concept in that the number assigned is not guaranteed to be sequential. The identity column in Teradata is simply used to guaranteed row-uniqueness.

Example:

CREATE MULTISET TABLE MyTable
  (
   ColA INTEGER GENERATED BY DEFAULT AS IDENTITY
       (START WITH 1
        INCREMENT BY 20)
   ColB VARCHAR(20) NOT NULL
  )
UNIQUE PRIMARY INDEX pidx (ColA);

Granted, ColA may not be the best primary index for data access or joins with other tables in the data model. It just shows that you could use it as the PI on the table.

Solution 3

This works too:

create table temp1 as 
( 
   select
   ROW_NUMBER() over( ORDER BY col1 ) insert_num
   ,col1
   ,col2
   ,col3
   from a join b on a.id=b.id
) with data ;

Comments

[HerdplattenToni]

google says CSUM(1,1) instead of rownum should do if your tables aren't to big...

[JMichael]

Rob, how would one then use an INSERT INTO to load MyTable?

[JMichael]

Update, realized the issue, I was missing a preceding commma. So for those who hit the same snag, assuming your generated column is column 1, you would use INSERT INTO <table name> VALUES(,?,?,<add enough ?'s for your case>);