Set sequential non-identification numbers in SQL

Question

I am hoping for some tips on how best to insert numbers into strings and automatically increase their sequence. I am using SQL Server 2014. I know that there is a sequence object. I played with temporary tables and what not, but I just have problems with this. The following are the data I'm working with:

Table1: OrderList
Column: LineNumber

Table2: WatchList
Column: LineNumber

The line number in the OrderList table is automatically generated by another system and results in 10k numbers. So, 10000000000000000.

The data from the list of orders is updated in the observation table. Then new lines are added, but LineNumber is not. Therefore, in the WatchList table, it looks like this:

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

, , 30000 40000 , , .

- , , .

Answer 1

CTE Row_Number().

@YourTable - .

, over (Order by Item) . over (Order by (Select NULL)) .

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

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