分批执行sql

select from CheckPartDetail d, CheckPartMaster m where d.CheckNo = m.CheckNo and m.CheckDate >=’2021-08-02’ and m.CheckDate <=’2021-08-03’ and RealAmount >0 and RealPrice =0


select BookPrice, from CheckPartDetail d, CheckPartMaster m where d.CheckNo = m.CheckNo
and m.CheckDate >=’2021-08-02’ and m.CheckDate <=’2021-08-03’ and RealAmount >0 and RealPrice =0



select distinct partno from CheckPartDetail d, CheckPartMaster m where d.CheckNo = m.CheckNo
and m.CheckDate >=’2021-08-02’ and m.CheckDate <=’2021-08-03’ and RealAmount >0 and RealPrice =0


—————————————生成临时数据
drop table #t
select d.rid into #t from CheckPartDetail d, CheckPartMaster m where d.CheckNo = m.CheckNo
and m.CheckDate >’2021-08-02’ and m.CheckDate <’2021-08-03’ and RealAmount >0 and RealPrice =0

drop table #abc1
select distinct partno,stockno,convert(numeric(24,6),0.0) as amount,convert(numeric(24,6),0.0)as price,convert(numeric(24,6),0.0) as money,convert(varchar(20),’’) as location into #abc1 from CheckPartDetail d, CheckPartMaster m where d.CheckNo = m.CheckNo
and m.CheckDate >’2021-08-02’ and m.CheckDate <=’2021-08-03’ and RealAmount >0 and RealPrice =0 and stockno = ‘01’
drop table #abc2
select distinct partno,stockno,convert(numeric(24,6),0.0) as amount,convert(numeric(24,6),0.0) as price,convert(numeric(24,6),0.0) as money,convert(varchar(20),’’) as location into #abc2 from CheckPartDetail d, CheckPartMaster m where d.CheckNo = m.CheckNo
and m.CheckDate >’2021-08-02’ and m.CheckDate <=’2021-08-03’ and RealAmount >0 and RealPrice =0 and stockno = ‘001’
alter table #abc1 add sIndex int IDENTITY(1,1) ,BookAmount numeric(24,6),BookPrice numeric(24,6),BookMoney numeric(24,6),DiffAmount numeric(24,6),DiffMoney numeric(24,6)
alter table #abc2 add sIndex int IDENTITY(1,1) ,BookAmount numeric(24,6),BookPrice numeric(24,6),BookMoney numeric(24,6),DiffAmount numeric(24,6),DiffMoney numeric(24,6)
update a1 set amount = b.amount,location = b.Location,bookamount = b.amount,bookprice = b.price,bookmoney=b.money
from #abc1 a1,PartsStock b
where a1.partno = b.partno and a1.stockno = b.stockno

update a2 set amount = b.amount,location = b.Location,bookamount = b.amount,bookprice = b.price,bookmoney=b.money
from #abc2 a2,PartsStock b
where a2.partno = b.partno and a2.stockno = b.stockno

update a1 set price = b.bookprice
from #abc1 a1,(
select partno,stockno,BookPrice from CheckPartDetail d, CheckPartMaster m where d.CheckNo = m.CheckNo
and d.rid in (
select max(d.rid) as rid from CheckPartDetail d, CheckPartMaster m where d.CheckNo = m.CheckNo
and m.CheckDate >’2021-08-02’ and m.CheckDate <=’2021-08-03’ and BookPrice > 0
group by partno,stockno
)) b
where a1.partno = b.partno and a1.stockno = b.stockno

update a2 set price = b.bookprice
from #abc2 a2,(
select partno,stockno,BookPrice from CheckPartDetail d, CheckPartMaster m where d.CheckNo = m.CheckNo
and d.rid in (
select max(d.rid) as rid from CheckPartDetail d, CheckPartMaster m where d.CheckNo = m.CheckNo
and m.CheckDate >’2021-08-02’ and m.CheckDate <=’2021-08-03’ and BookPrice > 0
group by partno,stockno
)) b
where a2.partno = b.partno and a2.stockno = b.stockno

select from #abc1 where price=0
select from #abc2 where price=0
—再找最后一次进价
update a1 set price = b.taxprice
from #abc1 a1,(
select partno,stockno,taxprice from PurchPartDetail
where rid in (
select max(rid) as rid from PurchPartDetail d where exists(
select 1 from (
select partno,stockno from #abc1 where price=0
union all select partno,stockno from #abc2 where price=0) aaa where d.PartNo = aaa.PartNo and d.stockno = aaa.stockno
)
group by partno,stockno
)
) b
where a1.partno = b.partno and a1.stockno = b.stockno

update a2 set price = b.taxprice
from #abc2 a2,(
select partno,stockno,taxprice from PurchPartDetail
where rid in (
select max(rid) as rid from PurchPartDetail d where exists(
select 1 from (
select partno,stockno from #abc1 where price=0
union all select partno,stockno from #abc2 where price=0) aaa where d.PartNo = aaa.PartNo and d.stockno = aaa.stockno
)
group by partno,stockno
)
) b
where a2.partno = b.partno and a2.stockno = b.stockno

update #abc1 set money = round(amountprice,2)
update #abc1 set DiffAmount=amount-bookamount,DiffMoney=money-bookmoney
update #abc2 set money = round(amountprice,2)
update #abc2 set DiffAmount=amount-bookamount,DiffMoney=money-bookmoney
drop table #price1
drop table #price2
select into #price1 from #abc1 where price=0
select into #price2 from #abc2 where price=0
select from #price1
union select from #price2
select into #price from #abc1 where price=0 and amount != 0
select from #abc2 where price=0and amount != 0

select from #abc1 where price > 0
select from #abc2 where price > 0
select from #abc2 where isnull(location,’’)=’’
select from Company
select from stockinfo
———————————调整语句

declare @Count int
declare @ListCount int
declare @Index int
declare @PerCount int
declare @ResultCode int
declare @CheckNo varchar(50)
declare @Msg varchar(200)
declare @CompNo varchar(8)
declare @StockNo varchar(20)


set @ListCount = 0
set @Count = 0
set @Index = 1
set @PerCount = 500
set @CompNo = ‘G002’
set @StockNo = ‘01’




select @Count = Count(1) from #abc1
set @ListCount = ceiling( convert(numeric(24,10),@Count) / @PerCount) + 1


while(@Index < @ListCount)
begin

set @CheckNo = ‘’
set @Msg = ‘’
Exec @ResultCode = Sp_GetSerialNo ‘M4003’,@CompNo,@CheckNo output


Insert into PartListDetail(ListNo,PartNo,Location,BookAmount,BookPrice,BookMoney,RealAmount,RealPrice,RealMoney,DiffAmount,DiffMoney, Remark)
select @CheckNo, PartNo,Location,bookAmount,bookPrice,bookMoney,amount,Price,money,diffamount,diffmoney,’20210802批量盘点’
from #abc1 where sindex >= 1+(@Index - 1)@PerCount and sindex <= @Index @PerCount order by sIndex


Insert Into PartListMaster(ListNo, CompNo,ListCode,ListDate, CreateDate, StockNo, remark,AmountSum,MoneySum)
select @CheckNo,@CompNo,’M4003’,GETDATE(),GETDATE(),@StockNo, ‘20210802批量盘点’,SUM(DiffAmount),SUM(DiffMoney)
from PartListDetail where ListNo = @CheckNo


exec Sp_CheckListFinish @CheckNo,’admin’,@Msg OutPut
—print(@Msg)
print(@CheckNo)


set @Index = @Index + 1
end
——————————-第2个库

declare @Count int
declare @ListCount int
declare @Index int
declare @PerCount int
declare @ResultCode int
declare @CheckNo varchar(50)
declare @Msg varchar(200)
declare @CompNo varchar(8)
declare @StockNo varchar(20)


set @ListCount = 0
set @Count = 0
set @Index = 1
set @PerCount = 500
set @CompNo = ‘G001’
set @StockNo = ‘001’




select @Count = Count(1) from #abc2
set @ListCount = ceiling( convert(numeric(24,10),@Count) / @PerCount) + 1


while(@Index < @ListCount)
begin

set @CheckNo = ‘’
set @Msg = ‘’
Exec @ResultCode = Sp_GetSerialNo ‘M4003’,@CompNo,@CheckNo output


Insert into PartListDetail(ListNo,PartNo,Location,BookAmount,BookPrice,BookMoney,RealAmount,RealPrice,RealMoney,DiffAmount,DiffMoney, Remark)
select @CheckNo, PartNo,Location,bookAmount,bookPrice,bookMoney,amount,Price,money,diffamount,diffmoney,’20210802批量盘点’
from #abc2 where sindex >= 1+(@Index - 1)@PerCount and sindex <= @Index * @PerCount order by sIndex


Insert Into PartListMaster(ListNo, CompNo,ListCode,ListDate, CreateDate, StockNo, remark,AmountSum,MoneySum)
select @CheckNo,@CompNo,’M4003’,GETDATE(),GETDATE(),@StockNo, ‘20210802批量盘点’,SUM(DiffAmount),SUM(DiffMoney)
from PartListDetail where ListNo = @CheckNo


exec Sp_CheckListFinish @CheckNo,’admin’,@Msg OutPut
—print(@Msg)
print(@CheckNo)


set @Index = @Index + 1
end