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

wanlijunAsked:
Who is Participating?
 
yuchingCommented:
try this

Select Distinct cust_num,
  Count(cust_num)as No_of_orders,
  Sum(order_lines)as No_of_Lines,
  Sum(Qty_Ordered) as TotalQty
From(
      select co.cust_num,co.co_num,
            count(co.co_num)as Order_Lines,
            sum(coitem.qty_ordered) as Qty_Ordered
      from co
      inner join coitem on co.co_num = coitem.co_num
      inner join (
            Select distinct (co.cust_num),do_seq.ref_num
            from co
            inner join do_seq on co.co_num = do_seq.ref_num
      )tempA on co.cust_num = tempA.cust_num and co.co_num = tempA.ref_num
      group by co.cust_num,co.co_num
)
Group by cust_num order by cust_num
0
 
Anthony PerkinsCommented:
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
 
0
 
wanlijunAuthor Commented:
Thanks so much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.