truncate table KnWarehouseItem
insert into KnWarehouseItem
select warehouseid,itemid,sum(stockqty) as qty from knmatlstock group by warehouseid,itemid
insert into KnWarehouseItem
select warehouseid,itemid,sum(stockqty) as qty from knmatlstock group by warehouseid,itemid
解决方案 »
- 视图有什么用?什么时候需要用到视图?
- (急)有關BOM結構表設計問題(組裝類型)
- 在数据库表中如何存放比例呀?25%,是存成0.25好呀,还是存成25好。还有,如何显示百分号?
- 问个一般性的问题 关于存储过程的 希望大家帮忙
- 这样的SQL语句如何写?
- 如何优化下面的select语句
- sql 的一个疑难杂症,有可能是这样吗?大牛请进!
- “超时已过期”救命啊!!!
- +==]======>■■■考验SQL语句■■■
- 大神求教:SQL Server 2012 总是阻塞,并且回滚特别慢,以及索引优化的问题
- 请问我如何建立三个表的极联约束??(t1->t2,t1->t3,t2->t3)
- 急急!!!关于ole驱动的问题150分求解(可以单独得分)在线等待!!!解决问题马上结帖!!!!!
ASSET NOCOUNT ONBEGIN TRANdelete KnWarehouseIteminsert into KnWarehouseItem(itemid,knWarehouseID,CurrentQty)
select warehouseid,itemid,sum(stockqty) from knmatlstock
group by warehouseid,itemidCOMMIT TRAN
SET NOCOUNT OFFGO
AS
SET NOCOUNT ON
BEGIN TRAN
insert into KnWarehouseItem(itemid,knWarehouseID,CurrentQty)
select warehouseid,itemid,sum(stockqty) as qty
from knmatlstock
group by warehouseid,itemidCOMMIT TRAN
SET NOCOUNT OFF
GO
AS
SET NOCOUNT ON
BEGIN TRAN
truncate table KnWarehouseItem --这样删除,速度才快
insert into KnWarehouseItem(itemid,knWarehouseID,CurrentQty)
select warehouseid,itemid,sum(stockqty) as qty
from knmatlstock
group by warehouseid,itemidCOMMIT TRAN
SET NOCOUNT OFF
GO
AS
SET NOCOUNT ON
BEGIN TRANdeclare @warehouseid int
declare @itemid int
declare @qty decimal
Declare t_line insensitive Cursor For
select warehouseid,itemid,sum(stockqty) as qty
from knmatlstock
group by warehouseid,itemidtruncate table KnWarehouseItem
Open t_line
Fetch next From t_line into @warehouseid,@itemid,@qtyWhile (@@fetch_status <> -1)
Begin
If (@@fetch_status <> -2) Begin
insert into KnWarehouseItem(itemid,knWarehouseID,CurrentQty)
values(@itemid,@warehouseid,@qty)
end
Fetch next From t_line Into @warehouseid,@itemid,@qty
end
drop table t
COMMIT TRAN
CLOSE t_line
DEALLOCATE t_line
SET NOCOUNT OFF
GO