All Forums Analytics
sathya 1 post Joined 11/04
20 Apr 2005
identity columns

need to generate a sequence number for every row inserted.Tried with identity columns but to no avail.Table A(has only one column)col1 1 2 3Table B(has two columns col1 and col2)col1 identity column generated always as identity-->(also tried other options)when I try to insert select table A data into table b it does not generate a sequenced number,but it generates a random number for every row.col1 col2100001 11 2200001 3Thank you

Witarsa 2 posts Joined 03/06
17 Mar 2006

What's the main difference between "GENERATED BY DEFAULT" and "GENERATED ALWAYS" commands?Tks,Marc

terauser1 2 posts Joined 03/06
17 Mar 2006

ALWAYS: Identity column values are always system generated.BY DEFAULT: Identity column values can be system generated or user inserted.You cannot insert values into, nor can you update, an identity column defined as GENERATED ALWAYS AS IDENTITY.

20 Apr 2006

A little late with this, but however:You can use the ROW_NUMBER ordered analytical function.So something likeSELECT ROW_NUMBER() OVER(order by col2),col2FROM tableBwill give you Col1 Col21 202 303 31Note that this will generate the row number based on the ordered list of Col2, so when you sort by col1 col2 will also be sorted.Paul

You must sign in to leave a comment.