Stored Procedure to Update Column in TableA to TableB Value +1 For Each Change In The Column

Question

I have a TableA that has an TransID I created to keep certain records together. Once those records have been reviewed, I need to assign TableA.DocNum to TableB.DocNum value +1

TableA

TransId	DocNum
5
5
6
6
7
7

TableB

DocNum
0000001

TableA Finished Result

TransId	DocNum
5	0000002
5	0000002
6	0000003
6	0000003
7	0000004
7	0000004

TableB Finished Result

TableB

DocNum
0000004

I have tried a loop but it didn't seem to work.

Answer 1

Here is an approach using dense_rank()

Example or dbFiddle

with cte as (
Select * 
      ,NewVal = format(B.Base+dense_rank() over (order by TransID),'0000000')
 from TableA A
 Cross Join ( Select Base=try_convert(int,max(DocNum)) from TableB) B
 Where A.DocNum is null
)
Update cte set DocNum=NewVal

Update TableB set DocNum = (select max(DocNum) from @TableA)

Select * from TableA
Select * from TableB

Results

TransID DocNum
4       0000001   -- Added for demo
5       0000002
5       0000002
6       0000003
6       0000003
7       0000004
7       0000004

DocNum
0000004