CREATE TABLE [dbo].[stockx](
[stockid] [int] default 0,
[itemcode] [int] default 0,
[expirecode] [int] default 0,
[totq] [float] default 0,
[netprice] [float] default 0 
) 

 
==========================
 
insert into stockx(totq,stockid,itemcode,expirecode,netprice)
select totq,stockid,itemcode,expirecode, (select netprice from  itemsnetprice where itemcode =jxj.itemcode) netprice from(
select sum(q) as totq,stockid,itemcode,expirecode from (
SELECT q, itemcode, expirecode, stockid FROM instock UNION ALL SELECT q, itemcode, expirecode, stockid FROM outstock
 UNION ALL SELECT q, itemcode, expirecode, stockid FROM taswyastock UNION ALL SELECT q, itemcode, expirecode, stockid  FROM transinstock UNION ALL SELECT q, itemcode, expirecode, stockid FROM transoutstock) jj group by stockid,itemcode,expirecode having sum(q) <>0)jxj 


==========================
 insert into purchases (stockid,itemcode,expirecode,netprice,price,inquantity )
select stockid,itemcode,expirecode,netprice,netprice as price,totq as inquantity from stockx where totq> 0