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 2008 Forums
 Transact-SQL (2008)
 AutoInsert Time Stamp Columns with 5 minutes Span

Author  Topic 

vijay1234
Starting Member

48 Posts

Posted - 2014-09-11 : 08:50:50
Hi Friends,

I have a requirement to create a table with 3 columns

ID (identity), Startdate(timestamp), Enddate(Timestamp)

a. Here the startdate should start with 2013-01-01 00:00:00.000
b. Enddate should end with 2013-01-01 00:05:00.000
c. Means for ID 1 ---- 5 minutes interval
d. For ID 2 ------5 minutes gap from 2013-01-01 00:06:00.000 till 2013-01-01 00:10:00.000................
e. That means for every hours there will be 12 such intervals
f. For a day there will be 288 rows.
.....

I need a sql script which will insert the data for the whole 2013 year,2014 & 2015 year automatically.

Kindly help me with this my dear friends.

Thanks
Vijay

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-11 : 09:45:55
Here's an easy way to generate the dates. Uncomment the insert and comment the "order by" to actually populate your table :


declare @startDate datetime = '2013-01-01 00:00:00.000';

with base(n) as (
select n from (values
(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
) v(n)
),
N(n) as (
select row_number() over(order by (select null)) from base b0, base b1, base b2
)
--insert into mytable (Startdate, Enddate)
select dateadd(minute, 5*(n-1), @startdate) as Startdate
, dateadd(minute, 5*n, @startdate) as Enddate
from N
where n <=3*288*365-1
order by n;
Go to Top of Page

vijay1234
Starting Member

48 Posts

Posted - 2014-09-11 : 09:58:04
Thanks Mate.

How ever it is inserting only 1000 records.

I would want to insert the whole 2013,2014,2015 records along with identity column in front.

:)
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-11 : 11:29:50
Yeah, look at the CTE: It only generates 1000 n's. It's good to understand it fully. You can just add:


,base b3, base b4, base b5, base b6

to the end

Here's an enhanced version to look at:


declare @startDate datetime = '2013-01-01 00:00:00.000';
declare @endDate datetime = '2016-01-01 00:00:00.000';
declare @intervals int = datediff(day, @startdate, @enddate)*12*24-1;

with base(n) as (
select n from (values
(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
) v(n)
),
N(n) as (
select 1 from base b0, base b1, base b2, base b3, base b4, base b5, base b6
),
numDates(n) as (
select top (@intervals) ROW_NUMBER() over (order by (select null)) from N
)

--insert into mytable (Startdate, Enddate)
select dateadd(minute, 5*(n-1), @startdate) as Startdate
, dateadd(minute, 5*n, @startdate) as Enddate
from numDates
order by n;
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-11 : 11:31:21
PS: I bet you can figure out how to populate the identity column!
Go to Top of Page

vijay1234
Starting Member

48 Posts

Posted - 2014-09-11 : 11:41:04
Got it Mate.

Thanks a ton :)

Go to Top of Page
   

- Advertisement -