WITH DateCTE
AS (
SELECT CAST('20100101' AS DATETIME) AS datevalue
UNION ALL
SELECT DATEADD(Second,1,datevalue)
FROM DateCTE
WHERE DATEADD(Second,1,datevalue) < CAST('20100101' AS DATETIME)+1
)
SELECT
REPLACE(CONVERT(varchar,datevalue,108),':','') AS TimeId
,DATEPART(HOUR,datevalue) AS [Hour]
,DATEPART(MINUTE,datevalue) AS [Minute]
,DATEPART(SECOND,datevalue) AS [Second]
INTO DimTime
FROM DateCTE D
OPTION(MAXRECURSION 0)