Looping in SQL Server from 1 to 60-CodePudding

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

CodePudding user response：

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;

CodePudding user response：

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

CodePudding user response：

Here is the result set I hope It solves your problem. 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;