The posts I made on date dimension table (Script for populating Date Dimension with Financial Year and Loading Data Script for Snowflake type Date Dimension) had a good response and few asked about the same for Time related table. This is one of the old scripts I used for populating Time Dimension, it maintains up to the second but if you need more, the script can be easily change.
Here is the code for creating the table and populating data.
CREATE TABLE dbo.Time
(
TimeKey int identity(1,1)
, Constraint pk_Time primary key (TimeKey)
, [Hour] smallint not null
, [24Hour] smallint not null
, [Minute] smallint not null
, [Second] smallint not null
, [Time] char(10) not null
, [24Time] char(8) not null
, HourBucket char(9)
);
GO
DECLARE @Hour smallint = 0
DECLARE @Minute smallint = 0
DECLARE @Second smallint = 0
WHILE (@Hour < 24)
BEGIN
WHILE (@Minute < 60)
BEGIN
WHILE (@Second < 60)
BEGIN
INSERT INTO dbo.[Time]
([Hour], [24Hour], [Minute], [Second], [Time], [24Time], HourBucket)
VALUES
(CASE WHEN @Hour > 12 THEN @Hour - 12 ELSE @Hour END
, @Hour, @Minute, @Second
, REPLICATE('0', 2 - LEN(CASE WHEN @Hour > 12 THEN @Hour - 1 ELSE @Hour END)) + CONVERT(varchar(2), CASE WHEN @Hour > 12 THEN @Hour - 1 ELSE @Hour END)
+ ':' + REPLICATE('0', 2 - LEN(@Minute)) + CONVERT(varchar(2), @Minute)
+ ':' + REPLICATE('0', 2 - LEN(@Second)) + CONVERT(varchar(2), @Second)
+ CASE WHEN @Hour > 12 THEN 'PM' ELSE 'AM' END
, REPLICATE('0', 2 - LEN(@Hour)) + CONVERT(varchar(2), @Hour)
+ ':' + REPLICATE('0', 2 - LEN(@Minute)) + CONVERT(varchar(2), @Minute)
+ ':' + REPLICATE('0', 2 - LEN(@Second)) + CONVERT(varchar(2), @Second)
, '[' + REPLICATE('0', 2 - LEN(@Hour)) + CONVERT(varchar(2), @Hour)
+ ' - '
+ REPLICATE('0', 2 - LEN(@Hour+1)) + CONVERT(varchar(2), @Hour+1) + ']')
SET @Second += 1;
END
SET @Second = 0;
SET @Minute += 1;
END
SET @Minute = 0;
SET @Hour += 1;
END
GO
SELECT * FROM dbo.[Time];
