List of all numbers between low and high

CREATE TABLE #TempBet
(High int, Low int)

INSERT INTO #TempBet (High, Low)
SELECT 1, 3 UNION ALL
SELECT 2, 3 UNION All
SELECT 5, 8

RESULT

Low High Bet
1 3 1
1 3 2
1 3 3
2 3 2
2 3 3
5 8 5
5 8 6
5 8 7
5 8 8

DROP TABLE #TempBet

CREATE TABLE #TempBet
(Id int, Low int, High int)

INSERT INTO #TempBet (Id, Low, High)
SELECT 1, 1, 3 UNION ALL
SELECT 2, 2, 3 UNION All
SELECT 3, 5, 8

; WITH Info (Id, Low, High, Result) AS (
SELECT Id, Min(Low), Max(High), Min(Low)
FROM #TempBet
GROUP BY Id
UNION ALL
SELECT Id, Low, High, Result + 1
FROM Info
WHERE Result < High )
SELECT * FROM Info
ORDER BY Id, Result

CREATE TABLE #TempBet
(Id int, Low numeric, High numeric)

INSERT INTO #TempBet (Id, Low, High)
SELECT 1, 10000000000, 30000000000 UNION ALL
SELECT 2, 20000000000, 30000000000 UNION All
SELECT 3, 50000000000, 80000000000

; WITH Info (Id, Low, High, Result) AS (
SELECT Id, Min(Low), Max(High), Min(Low)
FROM #TempBet
GROUP BY Id
UNION ALL
SELECT Id, Low, High, Result + 1
FROM Info
WHERE Result < High )
SELECT * FROM Info
ORDER BY Id, Result

-- DROP TABLE #TempBet

CREATE TABLE #TempBet
(Id int, Low bigint, High bigint)

INSERT INTO #TempBet (Id, Low, High)
SELECT 1, 10000050000, 10000080000 UNION ALL
SELECT 2, 10000070000, 10000090000 UNION All
SELECT 3, 50000050000, 50000090000 UNION All
SELECT 4, 50000060000, 50000070000

; WITH Info (Id, Low, High, Result) AS (
SELECT Id, Min(Low), Max(High), Min(Low)
FROM #TempBet
GROUP BY Id
UNION ALL
SELECT Id, Low, High, CAST(Result + 1 as bigint)
FROM Info
WHERE Result < High )
SELECT * FROM Info
ORDER BY Id, Result

CREATE TABLE #TempBet
(High int, Low int)

INSERT INTO #TempBet (Low, High)
SELECT 1, 3 UNION ALL
SELECT 2, 3 UNION All
SELECT 5, 8

;WITH Tally (n) AS (
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns t1 CROSS JOIN sys.all_columns t2)
SELECT High, Low, Bet=n
FROM #TempBet
CROSS APPLY (
SELECT n
FROM Tally
WHERE n BETWEEN Low AND High) a

DROP TABLE #TempBet

texpic (8/5/2012)

I have a reference table that I was given that has 50,000 records. It has ranges between two 11-digit numbers. I need to makes sure there are no overlaps. I was going to try and get the list of all the numbers in between the two ranges, then simply make sure they were all distinct.

For this example I cleaned up the ranges so they were less than 50,000 each; overlap is on purpose between the 2 lines. Changed all the numeric to bigint.

New error now, "Msg 530, Level 16, State 1, Line 12 The statement terminated. The maximum recursion 100 has been exhausted before statement completion."

--  DROP TABLE #TempBet

CREATE TABLE #TempBet
(Id int, Low bigint, High bigint)

INSERT INTO #TempBet (Id, Low, High)
SELECT 1, 10000050000, 10000080000 UNION ALL
SELECT 2, 10000070000, 10000090000 UNION All
SELECT 3, 50000050000, 50000090000 UNION All
SELECT 4, 50000060000, 50000070000 

; WITH Info (Id, Low, High, Result) AS (
SELECT Id, Min(Low), Max(High), Min(Low)
FROM #TempBet
GROUP BY Id  
UNION ALL
SELECT Id, Low, High, CAST(Result + 1 as bigint)
FROM Info
WHERE Result < High )
SELECT * FROM Info
ORDER BY Id, Result