数据库操作更改主键、循环插入

--alter table HistoryData drop constraint PK_HistoryData  -- 删除主键

alter table HistoryData add constraint PK_HistoryData primary key (CollectionTime,Code)--更改主键

ALTER TABLE HistoryData ALTER COLUMN [DataFlag] varchar(50) not NULL; --将空列转化为非空

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)
--删除重复的列。




循环插入
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表示分钟，hh小时，day 天


while  @maxdate <= cast(@eDate as datetime)
begin
set @mindateStr = convert(varchar(19),@mindate,120)
set @maxdateStr = convert(varchar(19),@maxdate,120)
--print(@mindateStr)
print(@maxdateStr)

--插数
--insert into
insert into HistoryData values(0.1337,'9001',@maxdate,'铁离子',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