--alter table Historydata Drop constraint pk_historydata--delete primary key
ALTER TABLE HISTORYDATA ADD constraint Pk_historydata primary KEY (Collectiontime,code)--Change primary key
Delete from Historydata where collectiontime between ' 2014-09-01 00:00:00 ' and ' 2014-11-01 00:00:00 ' and ID not in (
Select ID from (
Select Code,collectiontime,max (ID) ID from historydata
where collectiontime between ' 2014-09-01 00:00:00 ' and ' 2014-11-01 00:00:00 ' GROUP by Code,collectiontime) a)
--Delete the duplicate columns.
Loop insert
DECLARE @sDate varchar (20)
DECLARE @eDate varchar (20)
DECLARE @mindate datetime
DECLARE @maxdate datetime
DECLARE @mindateStr varchar (20)
DECLARE @maxdateStr varchar (20)
Set @sDate = ' 2015-1-16 06:30:00 '
Set @eDate = ' 2015-1-16 08:35:00 '
Set @mindate = Cast (@sDate as DateTime)
Set @maxdate = DateAdd (mi,5, @mindate)--mi represents minutes, hh hours, day days
While @maxdate <= cast (@eDate as DateTime)
Begin
Set @mindateStr = CONVERT (varchar), @mindate, 120)
Set @maxdateStr = CONVERT (varchar), @maxdate, 120)
--print (@mindateStr)
Print (@maxdateStr)
--Number of Inserts
--insert into
INSERT into historydata values (0.1337, ' 9001 ', @maxdate, ' iron ions ', 1,0.1337,0.1337, ' 2051 ', ' n ', ' ', ' 6001 ', ' n ', ' n ', ' n ', ' n ', 0, ' n ', ' n ')
Set @mindate = @maxdate
Set @maxdate = DateAdd (mi,5, @maxdate)
End
Database operations Change primary key, loop insert
