Quickest way to increment dates in inserted rows? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Quickest way to increment dates in inserted rows?

Author

Topic

PapillonUK
Starting Member

22 Posts

Posted - 2005-04-22 : 10:41:05

I'm trying to insert new rows in a table (sometimes over 10K new rows at a time) as quickly as possible.
The PK field is a DATETIME
When i do the INSERT I begin with a seed time

eg: '22 Apr 2005 08:55'

The first row must have this in the DATETIME field then the next row must have this time add 5 minutes

ie: '22 Apr 2005 09:00'

and so on for each row.

Currently, I have a WHILE loop similiar to:

SET @DateTime = '22 Apr 2005 08:55'
SET @ToDateTime = '22 May 2005 08:55'
WHILE @DateTime <= @ToDateTime
BEGIN
INSERT MyTable ([DateTime],[Blah1],[Blah2]...)
VALUES (@DateTime,@Blah1,@Blah2,...)
SET @DateTime = DATEADD(minute,5,@DateTime)
END

This seems to take AN AGE!
Can anyone think of a way to do this without the loop?

Thanks in advance for any help.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-04-22 : 11:18:45

I find it's really usefull to have a big table of sequential numbers starting at 0 to use for stuff like this. i.e.


DECLARE @start DATETIME
SET @start = '22 Apr 2005 08:55'
SELECT 
	DATEADD(MINUTE, n.number*5, @start)
FROM
	dbo.Number AS n
WHERE
	n.Number BETWEEN ? AND ?

You can get your start and end numbers using a DATEDIFF.
This way, you do a while loop once to generate your table of numbers, then replace it with a select every subsequent time you need to do something along these lines.

Mark

PapillonUK
Starting Member

22 Posts

Posted - 2005-04-22 : 13:19:58

Mark - i KNEW there had to be a better way!

I already had a table pre-populated with integers which i use for a left join for that horrible paging stuff in ASP.NET. The puritan in me always felt guiltly about this "bodge" ;-) but now you've shown me yet another use for it i don't feel as bad.

I've abandoned the loop and applied your method.
It has just reduced an insert of 15K rows from over 30 seconds to a fraction of one second!

Thank you! I lurve this forum!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-04-22 : 14:46:07

If you need to generate a number table, the in-line table function in this link will do it. You can save the numbers in a table. or generate it on the fly as you need it.

Here is a link to the function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources