Set Sequential Non-Identity Numbers in SQL

user_2221523 0
Viewed 53

I am hoping for some advice on the best way to achieve inserting numbers into rows and automatically increasing their sequence. I am using SQL Server 2014 I know there is the sequence object. I've played with temp tables and what not, but I'm just having trouble wrapping my mind around this. Below is the data I'm working with:

Table1: OrderList
Column: LineNumber

Table2: WatchList
Column: LineNumber

The LineNumber in the OrderList table is automatically generated by another system and results in numbers by 10k. So, 10000,20000,30000.

The data from the orderlist updates into the watchlist table. Then new rows are added, but the LineNumber is not. So in the WatchList table it looks like:

Item / LineNumber / xzy
134   10000   blah blah
1432  20000   blah blah blah
433   NULL    blah blah
839   NULL    blah blah

What I want to do is run an update to populate 30000 and 40000 into those null fields and I'm honestly not sure the best way to do it.

If you have some advice or a direction on which path would be best to take, I'd appreciate it.

sequence sql-server sql
1 Answers

You can update via a CTE and Row_Number().

The @YourTable is just a demonstrative table variable.

Also, the over (Order by Item) was an arbitrary choice. You could have over (Order by (Select NULL)) or someother sequence.

Example

Declare @YourTable Table (OrderNumber varchar(50),[Item] varchar(50),[LineNumber] int,[xzy] varchar(50))
Insert Into @YourTable Values 
 (2525,134,10000,'blah blah')
,(2525,1432,20000,'blah blah blah')
,(2525,433,NULL,'blah blah')
,(2525,839,NULL,'blah blah')
,(5050,500,70000,'blah blah')
,(5050,600,80000,'blah blah blah')
,(5050,700,NULL,'blah blah')


;with cte as (
    Select *
          ,RN =10000 * Row_Number() over (Partition By OrderNumber Order by Item) + (Select max(LineNumber) from @YourTable Where OrderNumber=A.OrderNumber)
     From  @YourTable A
     Where LineNumber is null
)
Update cte Set LineNumber = RN

Select * from @YourTable

The Updated Source

OrderNumber Item    LineNumber  xzy
2525        134     10000       blah blah
2525        1432    20000       blah blah blah
2525        433     30000       blah blah
2525        839     40000       blah blah
5050        500     70000       blah blah       --<< Notice New OrderNumber
5050        600     80000       blah blah blah
5050        700     90000       blah blah
user_15700000
Related