SQL Insert Set of Values Optimized

The goal is to create a table with sample data in Teradata for a year. A way to do it, is to copy the first 6 entries of similar data and just alter the timestamp 365 times (for my usecase). I didn't know better and wrote a procedure

REPLACE PROCEDURE stackoverflow()
BEGIN
    DECLARE i INTEGER DEFAULT 0;
    DELETE FROM TestTable;
    WHILE i < 365 DO
        INSERT INTO TestTable
            SELECT
                TestName
                ,Name_DT + i
            FROM
                (SELECT TOP 6
                    *
                FROM TestTable2
                WHERE Name_DT = '2021-12-15') AS sampledata;
        SET i = i + 1;
    END WHILE;
END;

This works, but is awfully slow. Also the IT department doesn't want us to use procedures. Is there a better way to achieve the same result without a procedure?

Solution 1:

The generic way to get repeated data is a CROSS JOIN:

SELECT
    TestName
    ,calendar_date
FROM
    ( SELECT TOP 6 *        
      FROM TestTable2
      WHERE Name_DT = DATE '2015-12-15'
    ) AS sampledata
CROSS JOIN 
  ( SELECT calendar_date
    FROM sys_calendar.CALENDAR
    WHERE calendar_date BETWEEN DATE '2011-12-15' 
                            AND DATE '2011-12-15' + 364
  ) AS cal
;

In your case there's Teradata's proprietary EXPAND ON syntax to create time series:

SELECT TestName, Begin(pd)
FROM 
        ( SELECT TOP 6 *        
          FROM TestTable2
          WHERE Name_DT = DATE '2015-12-15'
        ) AS sampledata
--  create one row per day in the range 
EXPAND ON PERIOD(Name_DT, Name_DT +365) AS pd