Here's a dbfiddle for the recursive cte
Not particularly fond of recursive queries when I can't be 100% sure of how deep the recursion needs to go, so wanted a to look at how to do this without recursion.
The gist of this idea is to find the start/end points of the gaps, and use a sequence generator to fill in the missing OrderIDs.
NOTE: I've discarded my initial sequence generator and replaced with @Daniel's sequence generator.
with -- build a list of all OrderIDs allids (SellerID, OrderID) as (select SellerID, OrderID from Orders union select SellerID, OID from Orders where OID is not NULL), -- find the first 'missing' OrderID for each gap gapStart (SellerID, startOrderID) as (select a1.SellerID, a1.OrderID + 1 from allids a1 where not exists(select 1 from allids a2 where a2.OrderID = a1.OrderID + 1)), -- now find the end of each gap gaps (SellerID, startOrderID, endOrderID) as (select gs.SellerID, gs.startOrderID, min(OrderID) - 1 from gapStart gs join allids a on a.SellerID = gs.SellerID and a.OrderID > gs.startOrderID group by gs.SellerID, gs.startOrderID) -- display the gaps select g.SellerID, g.startOrderID + n.rn - 1 as OrderID from gaps g -- generate a sequence (1..N) where N = difference between gaps.startOrderID and gaps.endOrderID cross apply (select top(g.endOrderID - g.startOrderID + 1) row_number() over (order by (select NULL)) as rn from Orders) as n order by 1,2; SellerID | gapID -------- | ------ seller1 | 123454 seller1 | 123461 seller1 | 123462 seller1 | 123464 seller1 | 123465 seller1 | 123467 seller1 | 123468 seller1 | 123469 seller2 | 234571
Here's a dbfiddle for the gap/sequence idea.