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.
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
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
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.
'Insert into table1(col1,col2,....rwid)
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.
Sry for pitching in middle. Correct me if my understanding is wrong :-)
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.