fill in numbers given start and end

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

fill in numbers given start and end

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2014-08-12 : 15:14:29

Greetings all

Given two sets of numbers let's say 100000 and 9999999 how could one generate into a temp table the number in between?

Here is my scenario: trying to feed some data to SOLR but would rather avoid hitting the source table but instead capture the smallest and the largest identity column value in our inventory table and then use temp table to generate the rest in between. This is just a workaround until we improve underlying issues too many to mention here

Thanks!

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-12 : 22:00:40

Seehttp://www.sqlservercentral.com/articles/T-SQL/62867/ on Tally tables

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-13 : 02:27:44

[code]CREATE FUNCTION dbo.GetNums
(
@Low BIGINT,
@High BIGINT
)
RETURNS TABLE
AS

RETURN WITH
L0 AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)),
L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum FROM L5)
SELECT TOP(@High - @Low + 1)
@Low + RowNum - 1 AS n
FROM Nums
ORDER BY RowNum;[/code]SELECT * FROM dbo.GetNums(1, 100000);

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources