How to expand rows from count only in tsql

Question

I have a table containing a number and a range value. For example, one column has a value of 40, and another column has a value of 100, which means that starting from 40, the range has 100 values ​​ending in 139, including the number 40. I want to write a tsql statement that expands my data into separate rows.

I think I need a cat for this, but I don’t know how I can achieve this.

Note: when expanding, I expect 7m lines.

Answer 1

If you need a CTE, here is an example:

Initial insertion:

insert into rangeTable (StartValue, RangeValue)
select 40,100
union all select 150,10
go

request:

with r_CTE (startVal, rangeVal, generatedVal)
as
(
    select r.startValue, r.rangeValue, r.startValue
    from rangeTable r
    union all
    select r.startValue, r.rangeValue, generatedVal+1
    from rangeTable r
    inner join r_CTE rc 
        on r.startValue = rc.startVal
        and r.rangeValue = rc.rangeVal
        and r.startValue +  r.rangeValue > rc.generatedVal + 1
)
select * from r_CTE
order by startVal, rangeVal, generatedVal

, 100. 32767,

option (maxrecursion 32767)

option (maxrecursion 0)

. BOL

Answer 2

, , , :

   SET NOCOUNT ON

   DECLARE @MaxValue INT
   SELECT @MaxValue = max(StartValue + RangeValue) FROM MyTable

   DECLARE @Numbers table ( 
      Number INT IDENTITY(1,1) PRIMARY KEY 
   )

   INSERT @Numbers DEFAULT VALUES 

   WHILE COALESCE(SCOPE_IDENTITY(), 0) <= @MaxValue 
      INSERT @Numbers DEFAULT VALUES 

    SELECT n.Number
    FROM   @Numbers n
    WHERE  EXISTS(
        SELECT *
        FROM   MyTable t
        WHERE  n.Number BETWEEN t.StartValue AND t.StartValue + t.RangeValue - 1
    )

    SET NOCOUNT OFF

, Numbers . .

Answer 3

:

create function [dbo].[fRange](@a int, @b int)
    returns @ret table (val int)
as 
begin
    declare @val int
    declare @end int
    set @val = @a
    set @end = @a + @b
    while @val < @end
    begin
        insert into @ret(val)
        select @val
        set @val = @val+1
    end
return
end

go

declare @ranges table(start int, noOfEntries int)

insert into @ranges (start, noOfEntries)
select 40,100
union all select 150, 10

select * from @ranges r
    cross apply dbo.fRange(start,noOfEntries ) fr

,

Answer 4

- splattne...

SET NOCOUNT ON

DECLARE @MaxValue INT
DECLARE @Numbers table (
    Number INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
 )

SELECT @MaxValue = max(RangeValue) FROM MyTable
INSERT @Numbers DEFAULT VALUES

WHILE COALESCE(SCOPE_IDENTITY(), 0) <= @MaxValue
    INSERT @Numbers DEFAULT VALUES

SELECT
    t.startValue + n.Number
FROM
    MyTable t
INNER JOIN
    @Numbers n
        ON n.Number < t.RangeValue

SET NOCOUNT OFF

, , "" ...

By the nature of the query, the table in the source table does not need to be indexed, but the "number" table must have an index (or primary key). Clustered Indexes refer to how they are stored on disk, so I don't see CLUSTERED here, but I left it because I just copied it from Splattne.

(Large connections like this can be slow, but still much faster than millions of inserts.)