asked on

Change Temporary Table to variable

can someone help me to change the temporary table into a temperary value. save and load the data from memory has the fast response time and reduce the cpu usage.

Select distinct (co.cust_num),do_seq.ref_num
into #TempA
from co 
inner join do_seq on co.co_num = do_seq.ref_num
 
select co.cust_num,co.co_num,count(co.co_num)as Order_Lines,sum(coitem.qty_ordered) as Qty_Ordered
into #TempB
from co
inner join coitem on co.co_num = coitem.co_num
inner join #TempA on co.cust_num = #TempA.cust_num and co.co_num = #TempA.ref_num
group by co.cust_num,co.co_num
 
drop table #TempA
 
select distinct cust_num, 
count(cust_num)as No_of_orders,
sum(order_lines)as No_of_Lines,sum(Qty_Ordered) as TotalQty 
into #TempC 
from #TempB 
Group by cust_num order by cust_num
 
drop table #TempB
 
select * from #tempc
 
drop table #TempC

Open in new window

ASKER CERTIFIED SOLUTION

yuching

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

This is what you asked for, however I don't suspect this is the solution you need:

Declare @TempA table (                                          -- Change data types appropriately
                              cust_num varchar(20),
                              ref_num varchar(20)
                        )

Insert      @TempA (cust_num, ref_num)
Select      distinct
            c.cust_num,
            s.ref_num
from      co c
            inner join do_seq s on c.co_num = s.ref_num

Declare @TempB table (                                          -- Change data types appropriately
                              cust_num varchar(20),
                              co_num varchar(20),
                              Order_Lines integer,
                              Qty_Ordered integer
                        )

Insert      @TempB (cust_num, co_num, Order_Lines, Qty_Ordered)
select      c.cust_num,
            c.co_num,
            count(*) Order_Lines,
            sum(i.qty_ordered) Qty_Ordered
from      co c
            inner join coitem i on c.co_num = i.co_num
            inner join @TempA a on c.cust_num = a.cust_num and c.co_num = a.ref_num
group by
            c.cust_num,
            c.co_num

Declare @TempC table (                                          -- Change data types appropriately
                              cust_num varchar(20),
                              No_of_orders integer,
                              No_of_Lines integer,
                              TotalQty integer
                        )

Insert      @TempC (cust_num, No_of_orders, No_of_Lines, TotalQty)
select      cust_num,
            count(*) No_of_orders,
            sum(order_lines) No_of_Lines,
            sum(Qty_Ordered) as TotalQty
from      @TempB
Group by cust_num
order by cust_num

wanlijun

ASKER

Thanks so much