The recursive solution may not provide your optimal performance.
You can try to use a Number table without temp table in one run. I don't have the setup to test the query but you may update this thread to let us know.
--===== Create number table on-the-fly
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n <101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
,mycte1 as (
SELECT STATION_NODE_CODE,TRADING_DATE,MAX(TRADING_PERIOD) AS maxTRADING_PERIOD
FROM dbo.FCT_SCADA_GENERATION t
GROUP BY TRADING_DATE,STATION_NODE_CODE
)
,mycte2 as (
select STATION_NODE_CODE,TRADING_DATE,n as TRADING_PERIOD from mycte1 m
Cross apply nums where nums.n<=m.maxTRADING_PERIOD
)
Select C.STATION_NODE_CODE,C.TRADING_DATE,C.TRADING_PERIOD
from mycte2 C
LEFT JOIN dbo.FCT_SCADA_GENERATION T
ON C.STATION_NODE_CODE=T.STATION_NODE_CODE
AND C.TRADING_DATE=T.TRADING_DATE
AND C.TRADING_PERIOD=T.TRADING_PERIOD
WHERE T.TRADING_PERIOD IS NULL AND ( C.TRADING_DATE > '1997-03-21' OR C.TRADING_PERIOD > 16 )