Looping through 2 temporary tables in SQL

Louise
Louise used Ask the Experts™
on
Im trying to loop through 2 temp tables to get results
temp table 1 returns product ids:
select iProductid, iRelationProdid, vdescription
into #tmpProduct
from tbProduct
where vcodealternative like 'xx'

eg returns
9153      9344      L1
9154      9345      L2


temp table 2 returns customer ids of people who've bought one of these products:
SELECT DISTINCT dbo.tbPurchase.iCustomerId
into #tmpCustomers
FROM         dbo.tbPurchaseItem INNER JOIN
                      dbo.tbPurchase ON dbo.tbPurchaseItem.iPurchaseId = dbo.tbPurchase.iPurchaseId INNER JOIN
                      dbo.tbProduct ON dbo.tbPurchaseItem.iProductId = dbo.tbProduct.iProductId INNER JOIN
                      dbo.tbCustomerGroup INNER JOIN
                      dbo.tbCustomer ON dbo.tbCustomerGroup.iCustomerId = dbo.tbCustomer.iCustomerId ON
                      dbo.tbPurchase.iCustomerId = dbo.tbCustomer.iCustomerId
WHERE     (dbo.tbPurchase.dtPurchase > CONVERT(DATETIME, '2010-06-01 00:00:00', 102)) AND (dbo.tbCustomerGroup.iGroupId = 1572) AND
                      (dbo.tbProduct.vCodeAlternative LIKE 'xx')
GROUP BY dbo.tbPurchase.iCustomerId

eg returns
1234
5678

Now what I need to do is
for each customer in tmpCustomer, for each row in tmpProduct find the last time they bought either a product matching iProductid or iRelationProdid within the time frame
and return the qty.  
I'd help with this part of the query in another post, now Im trying to put it together so it can be called
in sql rather than my external program

-- open a cursor that looks at each customer in tmpCustomer and each row in tmpProduct
-- for each customer
SELECT  PuI.iQty as TotalQty,  Pu.iCustomerId
       
  FROM  dbo.tbPurchaseItem as PuI
 INNER JOIN dbo.tbPurchase as Pu
    ON PuI.iPurchaseId = Pu.iPurchaseId
 WHERE Pui.iProductId in
 (select iProductid, iRelationProdid from #tmpProduct) -- do this for each row in #tmpProduct

and pu.icustomerid= #tmpCustomer.iCustomerid -- for each customer in #tmpCustomer
   and pu.dtpurchase >= '29 jun 2010'
  and pu.iPurchaseId = (select max(x.iPurchaseId)
                                       from dbo.tbpurchase as x
                                     Inner Join  dbo.tbpurchaseitem as xi
                                          on x.ipurchaseid=xi.ipurchaseid
                                    Where x.icustomerid=pu.icustomerid
                                         and (xi.iproductid in (select iProductid, iRelationProdid from #tmpProduct) )
                                   )

I could do this in loops in my asp code but it dramatically impact the database as there will be approximately 2K customers and 5 product rows per query.

Appreciate any help!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:

Look at these  at these 2 links your going to have to put your code inside a cursor


http://www.kodyaz.com/articles/t-sql-cursor-example-code.aspx

http://www.jackdonnell.com/articles/SQL_CURSOR.htm

something like this

define cursor
open cursor
while fetch (some condition)

code

fetch next from cursor
close cursor
LouiseSoftware developer

Author

Commented:
thanks ltorres32, it seems I'll need a cursor within a cursor which should be ok

do you know how I can create and update a temp table within these cursors so I can select them afterwards i.e. the values will still be there at the end of the cursors.

I've outlined my new query and have put comments where Im stuck :)

Thanks!

eg DECLARE CUST_CURSOR Cursor
      FOR select iCustomerid from #tmpCustomers

Open CUST_CURSOR  
DECLARE @Customerid int, @Productid int, @qty int,@iProductid, @iRelationProdid

Fetch NEXT FROM CUST_CURSOR INTO @Customerid
While (@@FETCH_STATUS <> -1)
BEGIN


-- code

 DECLARE PROD_CURSOR Cursor
      FOR select iProductid, iRelationProdid into #tmpProduct

      Open PROD_CURSOR  
      
      
      Fetch NEXT FROM PROD_CURSOR INTO @iProductid, @iRelationProdid
      While (@@FETCH_STATUS <> -1)
      BEGIN

      -- write each entry to some table that will exist when the loops end
                  SELECT  PuI.iQty as TotalQty,  Pu.iCustomerId
                        
                    FROM  dbo.tbPurchaseItem as PuI
                   INNER JOIN dbo.tbPurchase as Pu
                      ON PuI.iPurchaseId = Pu.iPurchaseId
                   WHERE Pui.iProductId in
                   (@iProductid, @iRelationProdid) -- do this for each row in #tmpProduct
                  
                  and pu.icustomerid= #tmpCustomer.iCustomerid -- for each customer in #tmpCustomer
                     and pu.dtpurchase >= '29 jun 2010'
                    and pu.iPurchaseId = (select max(x.iPurchaseId)
                                       from dbo.tbpurchase as x
                                     Inner Join  dbo.tbpurchaseitem as xi
                                          on x.ipurchaseid=xi.ipurchaseid
                                    Where x.icustomerid=pu.icustomerid
                                         and (xi.iproductid in ( @iProductid, @iRelationProdid) )
                                   )
--STUCK HERE

      -- NEED TO NOW ADD A ROW TO A TABLE THAT WILL HOLD ITS VALUES WHEN ALL CURSORS ARE CLOSED
      -- NEEDS TO HOLD THESE VALUES:
      -- iCustomerId,iProductid,TotalQty

      FETCH NEXT FROM PROD_CURSOR INTO @iProductid, @iRelationProdid
      END
      CLOSE PROD_CURSOR
      DEALLOCATE PROD_CURSOR


FETCH NEXT FROM CUST_CURSOR INTO @Customerid
END
CLOSE CUST_CURSOR
DEALLOCATE CUST_CURSOR

Commented:
Try

Select iCustomerId,iProductid,TotalQty
into #myTemptable
from SourceTable

Open in new window

Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Commented:
You can use any name you want as #myTemptable, but keep in mind this hashed table will disappear once the procedure closes..

if you need to store in a permanent table you can then do
Insert into permanetTable (iCustomerId,iProductid,TotalQty)
select iCustomerId,iProductid,TotalQty from #myTemptable


hope that help!!
LouiseSoftware developer

Author

Commented:
Thanks!

So could I go:

 Select iCustomerId = @icustomerid,iProductid = @iProductid,TotalQty = @TotalQty
into #myTemptable
 each time Im in the cursor loops?
ie @icustomerid from the outer cursor
@iProductid from the inner cursor
and @TotalQty from the query within the inner cursor.

Therefore it will append #myTemptable each time..

I can then do a select * once the cursors end

Commented:
Looks correct.. Did you declare and load cursor. If u do that will loop thru your code let me see if can get to pc

Commented:
Looks correct.. Did you declare and load cursor. If u do that will loop thru your code let me see if can get to pc

Commented:
Ok here is the code

Declare mycount cursor for
     Select iCustomerId = @icustomerid,iProductid = @iProductid,TotalQty = @TotalQty
     from SourceTable

open mycount
        Fetch from mycount into  @icustomerid_count,  @iProductid_count, @TotalQty_count

While (@@fetch_Status =0)
      Begin

      Fetch from mycount into  @icustomerid_count,  @iProductid_count, @TotalQty_count


any code that you want to loop should go in here use the references in mycount to get your values

     End
Close mycount
Deallocate mycount


Commented:
sorry
Replace
 Select iCustomerId = @icustomerid,iProductid = @iProductid,TotalQty = @TotalQty
     from SourceTable

with this one

 Select iCustomerId ,iProductid,TotalQty
     from SourceTable
Top Expert 2012

Commented:
Using a CURSOR is going to be a dog, why not do it in a simply query.  I cannot see any overrriding necessity for "Looping through" the rows of data.  But whatever works for you...
Top Expert 2012

Commented:
Incidentally if you are doing a GROUP BY there is no need to also do a DISTINCT.  SQL Server may be smart enough to know that it is redundant and ignore the DISTINCT, but it looks silly.
LouiseSoftware developer

Author

Commented:
Hi,
A big thanks for your help!
Almost there, the code that Im running is now:
select iProductid, iRelationProdid, vdescription
into #tmpProduct
from tbProduct
where vcodealternative like 'XX' and iProductGroupid = 320
order by iProductid



SELECT dbo.tbPurchase.iCustomerId
into #tmpCustomers
FROM         dbo.tbPurchaseItem INNER JOIN
                      dbo.tbPurchase ON dbo.tbPurchaseItem.iPurchaseId = dbo.tbPurchase.iPurchaseId INNER JOIN
                      dbo.tbProduct ON dbo.tbPurchaseItem.iProductId = dbo.tbProduct.iProductId INNER JOIN
                      dbo.tbCustomerGroup INNER JOIN
                      dbo.tbCustomer ON dbo.tbCustomerGroup.iCustomerId = dbo.tbCustomer.iCustomerId ON
                      dbo.tbPurchase.iCustomerId = dbo.tbCustomer.iCustomerId
WHERE     (dbo.tbPurchase.dtPurchase >= CONVERT(DATETIME, '29 jun 2010', 102)) AND (dbo.tbCustomerGroup.iGroupId = 1572) AND
                      (dbo.tbProduct.iProductid in (select iProductid from #tmpProduct) or dbo.tbProduct.iProductid in (select iRelationProdid from #tmpProduct))
GROUP BY dbo.tbPurchase.iCustomerId
order by dbo.tbPurchase.iCustomerId

 DECLARE CUST_CURSOR Cursor
      FOR select iCustomerid from #tmpCustomers

Open CUST_CURSOR  
DECLARE @Customerid int
declare @Productid int
declare @qty int
declare @iProductid int
declare @iRelationProdid int

Fetch NEXT FROM CUST_CURSOR INTO @Customerid
While (@@FETCH_STATUS <> -1)
BEGIN


-- code

 DECLARE PROD_CURSOR Cursor
      FOR select iProductid, iRelationProdid from #tmpProduct

      Open PROD_CURSOR  
      
      
      Fetch NEXT FROM PROD_CURSOR INTO @iProductid, @iRelationProdid
      While (@@FETCH_STATUS <> -1)
      BEGIN

      -- write each entry to some table that will exist when the loops end

                  SELECT  PuI.iQty as TotalQty,  Pu.iCustomerId,@iProductid as productid
                    -- into #tmpReport1  
                    FROM  dbo.tbPurchaseItem as PuI
                   INNER JOIN dbo.tbPurchase as Pu
                      ON PuI.iPurchaseId = Pu.iPurchaseId
                   WHERE Pui.iProductId in
                   (@iProductid, @iRelationProdid) -- do this for each row in #tmpProduct
                  
                  and pu.icustomerid= @Customerid--#tmpCustomers.iCustomerid -- for each customer in #tmpCustomer
                     and pu.dtpurchase >= '29 jun 2010'
                    and pu.iPurchaseId = (select max(x.iPurchaseId)
                                       from dbo.tbpurchase as x
                                     Inner Join  dbo.tbpurchaseitem as xi
                                          on x.ipurchaseid=xi.ipurchaseid
                                    Where x.icustomerid=pu.icustomerid
                                         and (xi.iproductid in ( @iProductid, @iRelationProdid) )
                                   )
      
      --select @CustomerId,@iProductid,TotalQty
      FETCH NEXT FROM PROD_CURSOR INTO @iProductid, @iRelationProdid
      END
      CLOSE PROD_CURSOR
      DEALLOCATE PROD_CURSOR


FETCH NEXT FROM CUST_CURSOR INTO @Customerid
END
CLOSE CUST_CURSOR
DEALLOCATE CUST_CURSOR

- works well in sql.
However returns separate rows/grids instead of one grid
how can I place these in a temp table then retrieve the temp table right at the end of the stored proc so my program can loop through each line
when I uncomment the line within the loops
  -- into #tmpReport1
I get this error:
There is already an object named '#tmpReport1' in the database.

I havent referenced the table before, if I comment the results come out in separate grids.. why does this happen and how can I overcome this?
LouiseSoftware developer

Author

Commented:
what I should clarify is that when the loop finishes I want to go
select * from tmpReport1

and send this back to my program..

Cheers
LouiseSoftware developer

Author

Commented:
Thank you.
realised I was creating the temp table within my cursor over and over :)

changed to
CREATE TABLE #tmpReport
(
   totalqty int null,
   customerid int null,
   productid int null
)
outside the loops
and
insert into #tmpReport values (@Qty,@CustomerId,@iproductid)
within.

Thanks for your help!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial