Sequnce generated column in Volatile table

Database
Enthusiast

Sequnce generated column in Volatile table

Hi All,

I need to have a unique column as row_id in a volatile table, with values to be populated based on the order of Insertion happened. I cannot have it based on the timestamp of insert, since more than one row may be inserted in a same fraction of second. Please suggest whether to have a sequence generated unique column or any other possible solution to acheive this.

Will a sequence generated column be possible in volatile table... Thanks in Advance.

Regards,

Bala.

5 REPLIES
Junior Contributor

Re: Sequnce generated column in Volatile table

Hi Bala,

are those inserts single row or insert/selects?

Dieter

Enthusiast

Re: Sequnce generated column in Volatile table

Hi Dieter,

they are Insert/selects only.. Selecting from some other source table. I want to convert a sybase code into TD. In sybase that source table itself is having the rowid based on the order of insert only. but thats not the case in teradata.. I dont think there will be a unique value assigned to each row in teradata internally at the time of insert/data migration.

Please confirm on the same/suggest any equivalent option in Td

Regards,

Bala.

Enthusiast

Re: Sequnce generated column in Volatile table

Hi, 

You can keep on increasing rowid column with this method.

INSERT INTO table2

SEL (SEL MAX(rowidcol) AS mx FROM table2) + ROW_NUMBER () OVER ( ORDER BY col2) AS idval, col2, col3 FROM table1

Thanks

Harpreet

Enthusiast

Re: Sequnce generated column in Volatile table

Hi Harpeet/Dieter,

I have created a volatile table with a column rwid declared for rowid insertion.

- I cannot use Identity column as it may result in gaps and its a volatile table too. 

- Use of CSUM is not recommended since it may affect the performance.

- ROW_NUMBER is not decisive as the columns in order by clause are not concluded.

I tried

 'Insert into table1(col1,col2,....rwid) 

SELECT  a.col1,

a.col2,

...

1+(SELECT MAX(rwid) FROM table1)

FROM table2 a '

 Its a Insert select statement.

Since MAX(rwid) returns null in case of no records in the table1 (initially), further additions result in null values only. Please suggest a possible solution on this.

Enthusiast

Re: Sequnce generated column in Volatile table

Sry for pitching in middle. Correct me if my understanding is wrong :-) 

Bala,

    put a coalesce in the max -- SELECT coalesce(MAX(rwid),1) FROM table1. But again you wont get the same sequence when you are doing the insert for the second time as like the first time.