identity columns

Analytics
N/A

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
3

Table 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 col2
100001 1
1 2
200001 3

Thank you

3 REPLIES

Re: identity columns

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

Tks,

Marc

Re: identity columns

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.
Enthusiast

Re: identity columns

A little late with this, but however:

You can use the ROW_NUMBER ordered analytical function.
So something like

SELECT ROW_NUMBER() OVER(order by col2),
col2
FROM tableB
will give you

Col1 Col2
1 20
2 30
3 31

Note 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