0

I have a Minutes table in my database and I want to insert all the minutes in 24hour format (starting from 00:00) into this table. I can do this using the following query but I am hoping to find a faster query to use instead of this very basic query that I came up with.

DECLARE @Start as Time(0) = CAST('00:00' as time)
DECLARE @ctr int = 0;
WHILE @ctr<>1440
BEGIN
INSERT INTO Minutes(Minute) VALUES(@Start)
SET @Start = DATEADD(minute, 1, @Start)
SET @ctr = @ctr + 1
END

The above query sometimes cause script timeout.

super-user
  • 1,017
  • 4
  • 17
  • 41
  • Why do you need something faster if you have to do this only once? However, look [here](http://stackoverflow.com/a/7090360/284240) – Tim Schmelter May 03 '16 at 08:25
  • @TimSchmelter I understand your point but I have a complex reason that I rather not lay down in full details just to justify the root of the question. (it will complicate a simple question). Lets just assume that I need a faster query because the Dark Lord will get mad to see my inefficient sql skills. – super-user May 03 '16 at 08:29

3 Answers3

2

Here is SET Based approach method using Tally table which does not require any looping or recursion. 

DECLARE @Start as Time(0) = CAST('00:00' as time)

;WITH e1(n) AS
(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), -- 10
e2(n) AS (SELECT 1 FROM e1 CROSS JOIN e1 AS b), -- 10*10
e3(n) AS (SELECT 1 FROM e1 CROSS JOIN e2), -- 10*100
e4(n) AS (SELECT 1 FROM e1 CROSS JOIN e3), 
tally (n) as ( SELECT n = ROW_NUMBER() OVER (ORDER BY n) FROM e4 )
SELECT Dateadd(minute, n - 1, @Start)
FROM   tally
WHERE  n <= 1440
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
  • 1
    wow, this did the job in 10ms, my original query took more than 1minute to execute. That's what I call efficient sql query. The dark lord will be pleased now. Thanks a lot buddy. – super-user May 03 '16 at 08:34
2

Try this, using a recursive CTE.

DECLARE @Start as Time(0) = CAST('00:00' as time)
;WITH CTE_TIME AS (
    SELECT  @Start STIME
    UNION ALL
    SELECT  DATEADD(MINUTE,1,STIME) 
    FROM CTE_TIME
    WHERE   DATEDIFF(MINUTE,@Start,STIME) < 1439
)
SELECT * FROM CTE_TIME
OPTION (MAXRECURSION 0)
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
Abdul Rasheed
  • 6,486
  • 4
  • 32
  • 48
  • 2
    though the Prdp's answer below was the first one to be posted, Im selecting this answer as it executed at same ms but the query is shorter. the Dark Lord will be amazed – super-user May 03 '16 at 08:38
  • 2
    @super-user - This will have a impact when you increase the range – Pரதீப் May 03 '16 at 08:40
  • 2
    @super-user - Here is an excellent article with performance comparison http://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1 – Pரதீப் May 03 '16 at 08:42
1

You simple could generate fields via Excel in following:

insert minutes

Pass to A1: INSERT INTO Minutes(Minute) VALUES(

Pass to B1: 1

Pass to C1 )

Select all 3 columns (A1; B1; C1) and left click on bottom-left corner of C1 and drag it down up to 1440 rows. It will generate rows in following:

INSERT INTO Minutes(Minute) VALUES(1)
INSERT INTO Minutes(Minute) VALUES(2)
INSERT INTO Minutes(Minute) VALUES(3)
.....

This way will be quick enough.

UPDATE

To do It with single query you can try in following:

single query

Stanislovas Kalašnikovas
  • 9,203
  • 4
  • 32
  • 59