需要从俩张表中统计一些信息,对源表也有相应要求,不得一次取多于100000条数据。初稿,有待修改。先保存到这里
create procedure p_license as
begin
declare @addnum numeric(4)
declare @tablestr char(4)
declare @sdate varchar(8)
declare @stime varchar(6)
declare @num mumeric(10)
create table #max_temp(sdate varchar(8),
num numeric(10),
timevalue varchar(6),
sid numeric(20)
)
set @addnum=5
while (@addnum > 0)
begin
declare @endid
declare @min_id numeric(20)
declare @max_id numeric(20)
select @tablestr=right('00'+convert(varchar,datepart(mm,dateadd(day,@addnum,getdate()))),2)
+right('00'+convert(varchar,datepart(dd,dateadd(day,@addnum,getdate()))),2)
begin
select @endid =select isnull(endid,0)+1
from license_log
where table_name='websubmitlog_t'+@tablestr
select @min_id=select min(billindex)
from 'websubmitlog_t'+@tablestr
where biilindex>=@endidif (@max_id-@min_id)>1000000
select @max_id=@min_id+1000001
insert into max_temp(sdate,num,timevalue,sid)
select left(convert(varchar,msgid),8) ,count(*) , substring(convert(varchar,msgid),9,14),billindex
from 'websubmitlog_t'+@tablestr
where billindex>@min_id
and billindex<=@max_id
group by substring(convert(varchar,msgid),9,14),left(convert(varchar,msgid),8),billindex
if @@error<>0
begin
insert into license_log(table_name,startid,endid,processtime,processed)
values('websubmitlog_t'+@tablestr,@min_id,@max_id,getdate(),0)
delect from max_temp where sid>@min_id
end
else
insert into license_log(table_name,startid,endid,processtime,processed)
values('websubmitlog_t'+@tablestr,@min_id,@max_id,getdate(),1)
insert into db..license(sdate,num,timevalue) select sdate,max(num) ,stime from #max_temp
truncate table #max_temp
end
begin
select @endid =select isnull(endid,0)+1
from license_log
where table_name='mmscdeliverlog_t'+@tablestr
select @min_id=select min(billindex)
from 'mmscdeliverlog_t'+@tablestr
where biilindex>=@endidif (@max_id-@min_id)>1000000
select @max_id=@min_id+1000001
insert into max_temp(sdate,num,timevalue,sid)
select left(convert(varchar,msgid),8) ,count(*) , substring(convert(varchar,msgid),9,14),billindex
from 'mmscdeliverlog_t'+@tablestr
where billindex>@min_id
and billindex<=@max_id
group by substring(convert(varchar,msgid),9,14),left(convert(varchar,msgid),8),billindex
if @@error<>0
begin
insert into license_log(table_name,startid,endid,processtime,processed)
values('mmscdeliverlog_t'+@tablestr,@min_id,@max_id,getdate(),0)
delect from max_temp where sid>@min_id
end
else
insert into license_log(table_name,startid,endid,processtime,processed)
values('mmscdeliverlog_t'+@tablestr,@min_id,@max_id,getdate(),1)
insert into db..license(sdate,num,timevalue) select sdate,max(num) ,stime from #max_temp
truncate table #max_temp
end
select @addnum=@addnum-1
end
end