CODEWITHSUNDEEP
sqlteradatarow-number
AFHood
AFHood

Reputation: 1040

Add a sequential number on create / insert - Teradata

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
;

Upvotes: 3

Views: 38922

Answers (3)

Ted Elliott
Ted Elliott

Reputation: 3493

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 ;

Upvotes: 1

Rob Paller
Rob Paller

Reputation: 7786

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.

Upvotes: 3

Carlos A. Ibarra
Carlos A. Ibarra

Reputation: 6132

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 ;

Upvotes: 7

Related Questions