Inserting x rows based off a column value

user_7080252 0
Viewed 40

I've had a look about and seen some examples, but none that suit my needs.

I have a table:

+-------+-----------+-----------+
| Year  |   Month   |   Count   |     
+-------+-----------+-----------+
| 2001  |    Nov    |    2      |   
| 2001  |    Dec    |    1      |   
| 2002  |    Jan    |    3      |  
+-------+-----------+-----------+

Now the idea is I want to insert the Year and Month values x times into another table based on the Count Value. So I should end up with something like the following:

+-------+-----------+
| Year  |   Month   |    
+-------+-----------+
| 2001  |    Nov    |   
| 2001  |    Nov    |  
| 2001  |    Dec    |   
| 2002  |    Jan    |   
| 2002  |    Jan    | 
| 2002  |    Jan    |  
+-------+-----------+

This is just a small sample, the actual table is much larger.

Any pointers on this would be great. I hope what I am asking makes sense, any questions, please ask :)

sql-insert sql-server sql
2 Answers

Using the tally table idea you can do this quite easily. Here is an example with your data.

declare @Something table 
(
    MyYear int
    , MyMonth char(3)
    , MyCount int
)
;

insert @Something values
(2001, 'Nov', 2)   
,(2001, 'Dec', 1)   
,(2002, 'Jan', 3)  
;

WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E1
    )

insert YourOtherTable
(
    YourYear
    , YourMonth
)
select s.MyYear
    , s.MyMonth
from @Something s
join cteTally t on t.N <= s.MyCount
user_38131160

You can also use a recursive cte as

WITH CTE AS
(
  SELECT *
  FROM T
  UNION ALL
  SELECT [Year], [Month], [Count] -1
  FROM CTE
  WHERE [Count]-1 > 0
)
SELECT T.*
FROM T LEFT JOIN CTE
       ON T.Year = CTE.Year
          AND
          T.Month = CTE.Month
user_64266920
Related