Friday, July 16, 2010

Transact SQL loop

This came quite handy today:

DECLARE @FROM DATETIME
DECLARE @TO DATETIME
DECLARE @TEMP DATETIME
TRUNCATE TABLE administration.dbo.HalfHours
SET @FROM = '2006-01-01 00:00:00'
SET @TO = '2012-01-01 00:00:00'
SET @TEMP = @FROM
WHILE @TEMP < @TO
BEGIN
PRINT @TEMP
INSERT INTO administration.dbo.HalfHours (XDateTime) VALUES (@TEMP);
SET @TEMP = DATEADD(mi,30,@TEMP)
END

CREATE INDEX IDX_ONE ON administration.dbo.HalfHours(XDateTime)
SELECT * FROM administration.dbo.HalfHours