Generate incremental sequence number

General
General covers Articles, Reference documentation, FAQs, Downloads and Blogs that do not belong to a specific subject area. General-purpose Articles about everything and anything
Enthusiast

Generate incremental sequence number

Hi Guys,

 

I have a scenario that there will be 2 tables.

1. scratch.data_table

2. scratch.temp_table

 

scratch.data_table wiill have one of the field as sequence_id in incremental way like 4000,4001,4002 

Other table   scratch.temp_table will have other columns same as scratch.data_table  but except sequence_id column

 

scratch.temp_table will have 5 records and I want to take max of sequence_id from scratch.data_table i.e 4002

and insert it into the same table scratch.data_table from 5 records of scratch.temp_table

 

Result should be like :-

 

4000 | a | b | c

4001 | x| y | z

4002 | s| t | d

4003 | d | t | f         --> d|t|f is the records from scratch.temp_table and 4003 is the incremental value

4004 | d| g|  h

4005 | g | t |h

4006 | y| u| i

4007 | y| y| t

 

 

Kindly let me know for any clarifications

It will be a great help if anyone will provide how to achieve this scenario

 

 

 


Accepted Solutions
Junior Contributor

Re: Generate incremental sequence number

select
    coalesce((select max(sequence_id) from data_table),0)
    + row_number() over (order by not_too_skewed_column_eg_PI)
 , t.*
from temp_table as t

Or you keep a sequence table where you store the current max in one row per table

 

 

1 ACCEPTED SOLUTION
2 REPLIES
Junior Contributor

Re: Generate incremental sequence number

select
    coalesce((select max(sequence_id) from data_table),0)
    + row_number() over (order by not_too_skewed_column_eg_PI)
 , t.*
from temp_table as t

Or you keep a sequence table where you store the current max in one row per table

 

 

Enthusiast

Re: Generate incremental sequence number

Thanks a lot :)