Insert unique value multiple times depending on variable column

Question

I have the following columns in my table:

PromotionID | NumberOfCodes 
1             10
2             5

I need to insert the PromotionID into a new SQL table a number of times - depending on the value in NumberOfCodes.

So given the above, my result set should read:

PromotionID 
1
1
1
1
1
1
1
1
1
1
2
2
2
2
2

Seems easy enough... What have you already tried? Please read How to Ask and the first couple of paragraphs in the tsql tag info and edit your question accordingly. — Zohar Peled
– Zohar Peled, Commented Mar 18, 2020 at 9:55
Hi Zohar - I've been directed towards nested loops, but not much experience with these. — BenW2811
– BenW2811, Commented Mar 18, 2020 at 9:59

Yogesh Sharma · Accepted Answer · 2020-03-18 09:55:55Z

2

You need recursive cte :

with r_cte as (
     select t.PromotionID, 1 as start, NumberOfCodes 
     from table t
     union all
     select id, start + 1, NumberOfCodes 
     from r_cte 
     where start < NumberOfCodes 
)
insert into table (PromotionID)
  select PromotionID
  from r_cte 
  order by PromotionID;

Default recursion level 100, use query hint option(maxrecursion 0) if you have NumberOfCodes more.

answered Mar 18, 2020 at 9:55

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BenW2811 Over a year ago

Thanks Yogesh - this gives me exactly what I need

Thom A Over a year ago

FYI, @BenW2811 if your max number is 20,000 then performance won't be as good as it good be. An rCTE, as it's name suggests, is recursive, and SQL is awful at recursive things. It excels at set based methods

Thom A · Accepted Answer · 2020-03-18 09:58:05Z

1

I prefer a tally for such things. Once you start getting to larger row sets, the performance of an rCTe degrades very quickly:

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2) --Up to 100 rows. Add more cross joins to N for more rows
SELECT YT.PromotionID
FROM dbo.YourTable YT
     JOIN Tally T ON YT.NumberOfCodes >= T.I;

answered Mar 18, 2020 at 9:58

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Comments

GMB · Accepted Answer · 2020-03-18 09:56:04Z

0

One option uses a recursive common table expression to generate the rows from the source table, that you can then insert in the target table:

with cte as (
    select promotion_id, number_of_codes n from sourcetable 
    union all select promotion_id, n - 1 from mytable where n > 1
)
insert into targettable (promotion_id)
select promotion_id from cte

answered Mar 18, 2020 at 9:56

GMB

224k25 gold badges103 silver badges151 bronze badges

Collectives™ on Stack Overflow

Insert unique value multiple times depending on variable column

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related