Introduce sequence number on Full row duplicates data

General
Enthusiast

Introduce sequence number on Full row duplicates data

Hi All,

I have a very wierd requirement that i need to load a file containing full row duplicate as shown below in to a table without losing any value and it should also contain a primary key defined. hence we are planning to use a sequence number such that each record will be treated as a unique record and can also define primary key on the sequence number.

col 1               col 2                        col 3                 seq num

aaa                bbb                          ccc                         1

aaa                bbb                          ccc                         2

one option i found is to use row_number() over (order by col1, col2, col3).

Since the table contains nearly 50 columns this query looks awkward, i am sure there could be a way to simplify the query. Kindly help me on this.

Regards

Jaganna

4 REPLIES
Enthusiast

Re: Introduce sequence number on Full row duplicates data

Hi All,

I found sum(1) over( rows unbounded preceding ) to be an interersting substitute, but i would like to hear some experts advice on using this condition.

Please advice.

Regards

Jaganna

Enthusiast

Re: Introduce sequence number on Full row duplicates data

Enthusiast

Re: Introduce sequence number on Full row duplicates data

Generated always as identity can be used, but you may not get it in sequence. it will be make the row unique and a good distribution.

col_name BIGINT GENERATED ALWAYS AS IDENTITY

           (START WITH 1

            INCREMENT BY 1

            MINVALUE -999999999999999999

            MAXVALUE 999999999999999999

            NO CYCLE)

and make it as PI.

Enthusiast

Re: Introduce sequence number on Full row duplicates data

Thanks raja, those links are pretty useful. i decided to use sum(1) over (rows unbounded precedings) and it works well.

Shrini, looks like identity column cannot be used as a primary key. I tried creating primary key based on the column and end up with Illegal use of identity column. Otherwise i felt it as a good approach as well.