Insert N Rows based on select value

Question

Basically, I have a table T with the following information

Col1 | Col2 | Factor
A      B      50
C      D      60

I now need to insert into T n rows duplicates of each row based on the Factor. In the first row I would have the following:

B | string | 1 (50x) (Where string is a hardcoded string)

How can I do it?

score 3 · Accepted Answer · answered Apr 13 '20 at 13:48

A simple way uses recursive CTEs:

with cte as (
      select col1, col2, factor, 1 as lev
      from t
      union all
      select col1, col2, factor - 1, lev + 1
      from cte
      where factor > 1
     )
select col1, col2, 1 as factor  -- or perhaps lev if you want an incrementing value
from cte;

Two notes:

You can use this for an insert or select into.
If factor can be more than 100, then add option (maxrecursion 0) to the query.

Recursive CTEs have surprisingly decent performance. But if you need to generate zillions of rows, then some sort of number/tally table would be useful. For instance, if your base table had enough rows:

select col1, col2, 1 as factor
from t join
     (select row_number() over (order by (select null)) as n
      from t
     ) n
     on n.n <= t.factor

score 1 · Answer 2 · answered Apr 13 '20 at 13:55

Here is an another option as in source:

Create table FactorTable (Col1 char(1)
 , Col2 char(1)
 , Factor int)

Insert into FactorTable Values
('A', 'B', 50),
('C', 'D', 60)

SELECT *
FROM FactorTable t
CROSS APPLY (SELECT n
             FROM (SELECT ROW_NUMBER() OVER(ORDER BY 1/0) AS n
                   FROM master..spt_values s1) AS sub
             WHERE  sub.n <= t.Factor) AS s2(Series);

Live db<>fiddle demo.

Insert N Rows based on select value

2 Answers2