Looping in SQL Server from 1 to 60

Question

I have a table T1 as below

I need to copy the data from T1 to another table called T2. T2 has an additional column called 'Month' and each record from T1 needs to be copied to T2 60 times, with Month value ranging from 1 to 60.

I have been trying something like this and need the MONTH value to be taken dynamically , like a loop from 1 to 60. Could someone help please? Thank you

INSERT INTO T2
    SELECT PRODUCT, CUSTOMER, 1 as MONTH
    FROM T1

Does this answer your question? [How to generate a range of numbers between two numbers?](https://stackoverflow.com/questions/21425546/how-to-generate-a-range-of-numbers-between-two-numbers) — Jamiec, Jul 04 '22 at 12:40
or https://stackoverflow.com/questions/45361887/how-can-i-create-a-temporary-numbers-table-with-sql — Jamiec, Jul 04 '22 at 12:41
One of the key skills to learn in working with SQL is to start thinking in terms of *sets*. Not in terms of loops, procedural code, and working Row-by-agonizing-row. You want to join your `T1` with a set of the numbers 1-60. Find a way to express that (as in Tim's answer) rather than thinking of writing a loop. However, be wary also of storing computed data - is there a reason you think you need to *store* `T2` if it's actually always "the same as T1 joined with the numbers 1-60"? — Damien_The_Unbeliever, Jul 04 '22 at 13:11

Tim Biegeleisen · Answer 1 · 2022-07-04T13:10:48.400

3

We can use a cross join approach:

WITH months AS (
    SELECT n = v2.n * 10 + v1.n
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v1(n)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6)) v2(n)
)

INSERT INTO T2 (Product, Customer, Month)
SELECT t1.Product, t1.Customer, m.n
FROM table1 t1
CROSS JOIN months m
WHERE m.n BETWEEN 1 AND 60;

edited Jul 04 '22 at 13:10

answered Jul 04 '22 at 12:42

Tim Biegeleisen

502,043
27
286
360

This will also generate a month 0 won't it? – Damien_The_Unbeliever Jul 04 '22 at 13:09
1

@Damien_The_Unbeliever Fixed. I am new to SQL, so am still learning the ropes. – Tim Biegeleisen Jul 04 '22 at 13:11

score 0 · Answer 2 · edited Jul 05 '22 at 01:20

0

I used stored procedures in MySQL:

    DELIMITER $$

CREATE PROCEDURE auto_insert()
    BEGIN
        DECLARE i1 INT DEFAULT 1;
        WHILE i1 <= 60 DO
            INSERT INTO T2 SELECT *, i1 FROM T1;    
            SET i1 = i1 + 1;
        END WHILE;
    END $$
DELIMITER ;

CALL auto_insert;

Here is the result set

edited Jul 05 '22 at 01:20

Jeremy Caney

7,102
69
48
77

answered Jul 04 '22 at 13:49

Ozan Sen

2,477
2
4
16

score 0 · Answer 3 · answered Jul 04 '22 at 13:52

0

CROSS JOIN to a tally, with the values 1 to 60. -- Thanks @Larnu for the answer.

answered Jul 04 '22 at 13:52

Newbie

713
2
10
19

Looping in SQL Server from 1 to 60

3 Answers3