Change Temporary Table to variable

Posted on 2009-02-09
Last Modified: 2012-05-06
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

Question by:wanlijun
    LVL 11

    Accepted Solution

    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
          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
    LVL 75

    Expert Comment

    by: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
    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,
                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

    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

    Author Closing Comment

    Thanks so much

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now