資料庫操作更改主鍵、迴圈插入，資料庫主鍵

資料庫操作更改主鍵、迴圈插入，資料庫主鍵
--alter table HistoryData drop constraint PK_HistoryData  -- 刪除主鍵
alter table HistoryData add constraint PK_HistoryData primary key (CollectionTime,Code)--更改主鍵




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