Let us begin with understanding what is a sequence number in a table.Sequence number in a database table is simply a column which has incremental value i.e. the new value will be last maximum value + 1 (in most cases it is incremented by 1 however you can change it also as per your requirement).
Sequence numbers are generally used as surrogate keys. Surrogate keys can help us in recognizing any record in a table uniquely.Major difference between Primary Keys and Surrogate keys is Surrogate Keys don’t store much information and mostly it is a number value only.
So let us see now how to implement sequence number in any Database Table using simple SQL.
Select csum(1,col2) + TGTMAX.MAX_ID as Col1,col2,col3 From SRCTable INNER JOIN ( SELECT MAX(Col1) AS MAX_ID FROM TGTTable ) TGTMAX ON 1=1
The above code can help you in implementing SEQUENCE NUMBERS in table using SQL very easily.And best part is you can edit the code as per your requirement.Here if we see our COL1 is the column in table TGTTable which is sequence number.The INNER JOIN clause at the end is fetching maximum value of col1 existing in TGTTable.SRCTable is my SOURCE Table and I am fetching all the required values from my SOURCE Table and can easily insert the output of the above query into my table.
The function CSUM simply assigns an incremental values to all the rows received.If there are 10 rows received it will assign values from 1-10 to rows.We need MAX in order to increment values from our last stored maximum value. In INNER JOIN we are giving condition as 1=1 which will make this condition true all the time and this JOIN condition will run every time we run the query.
Try this code and share your thoughts. If you want you can also create IDENTITY columns in Teradata which act as Sequence numbers and user need not load it while pushing rows into the table.