Skip to content
Advertisement

Inserting Multiple of same records into SQL temp table based on value in column

So I have table with the following records:

Source table

I want to create a script to iteratively look at the Cnt_Repeat column and insert that same record in a temp table X times depending on the value in Cnt_Repeat so it would look like the following table:

Table of results

Advertisement

Answer

One method supported by most databases is the use of recursive CTEs. The exact syntax might vary, but the idea is:

with cte as (
      select loannum, document, cnt_repeat, 1 as lev
      from t
      union all
      select loannum, document, cnt_repeat, lev + 1
      from cte
      where lev < cnt_repeat
     )
select loannum, document, cnt_repeat
from cte;
  
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement