identity columns

Analytics
Not applicable

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 3

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