combining the result of multiple cursor fetches

Question

combining the result of multiple cursor fetches

marcjason

Mr or Mrs. 500

Points: 501
More actions
October 21, 2013 at 3:23 am

#284735

Hi,
How would I combine the result of multiple selects using a cursor. My code is listed below. It currently returns 20 separate tables but I want to combine them into one.
DECLARE @GROUPID int
DECLARE @POLICYID int
DECLARE @ANSLID int
DECLARE @PERSPCODE varchar(2)
DECLARE myCursor CURSOR
FOR
SELECT GROUPID, POLICYID, [Analysis ID], PERSPCODE
FROM PC_Tool_Coding.dbo.ListPolicies_CB
OPEN myCursor
FETCH NEXT FROM myCursor
INTO @GROUPID, @POLICYID, @ANSLID, @PERSPCODE
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT@GROUPID GROUPID
, @POLICYID POLICYID
, rdm_port.EVENTID
, RATE
, SUM(PERSPVALUE) PERSPVALUE
, SUM(rdm_port.STDDEVC) STDDEVC
, SQRT(SUM(rdm_port.STDDEVI*rdm_port.STDDEVI)) STDDEVI
, SUM(EXPVALUE) EXPVALUE
, PERSPCODE
FROMPC_DIRECT_RDM_OCT13.dbo.rdm_port
LEFT JOIN PC_DIRECT_RDM_OCT13.dbo.rdm_anlsevent ON rdm_port.EVENTID = rdm_anlsevent.EVENTID
AND rdm_port.ANLSID = rdm_anlsevent.ANLSID
WHERErdm_port.ANLSID = @ANSLID
AND PERSPCODE IN (@PERSPCODE)
GROUP BY rdm_port.EVENTID
, RATE
, PERSPCODE
FETCH NEXT FROM myCursor
INTO @GROUPID, @POLICYID, @ANSLID, @PERSPCODE
END
CLOSE myCursor
DEALLOCATE myCursor
Many thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

kapil_kk SSC-Insane Points: 21316 More actions · Answer 1

Can you please provide some more details?

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

marcjason Mr or Mrs. 500 Points: 501 More actions · Answer 2

Yes of course. What details would you like? At the moment it executes 20 separate select queries as the fetch goes through the while loop. I just want to take the result of each select in the while loop and create 1 table. So if I was doing it as 20 separate select queries I would just union them all together.

If you let em know what details you would like I'll provide them.

Thanks

kapil_kk SSC-Insane Points: 21316 More actions · Answer 3

marcjason (10/21/2013)
Yes of course. What details would you like? At the moment it executes 20 separate select queries as the fetch goes through the while loop. I just want to take the result of each select in the while loop and create 1 table. So if I was doing it as 20 separate select queries I would just union them all together.
If you let em know what details you would like I'll provide them.
Thanks

Then why dont you create a temp table and insert data in that in cursor and store data for all 20 tables in a single table

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

marcjason Mr or Mrs. 500 Points: 501 More actions · Answer 4

because I don't know how to. I'm new to using cursors and I am not sure how I would do that. This is why I am asking in a forum

marcjason Mr or Mrs. 500 Points: 501 More actions · Answer 5

marcjason

Mr or Mrs. 500

Points: 501

October 21, 2013 at 7:22 am

#1660133

figured it out

Thanks for your help

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

You don't need to use a cursor at all. Just add an extra join in your inner query to to ListPolicies_CB. It'll (almost certainly) run quicker and use fewer resources on your server.

John

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

Hi,

I'm glad that you found how to solve your problem. However, we might be able to help you to eliminate the cursor and generate your results on a single statement (called as set-based soultion). This will perform much better and after some practice, it will become very simple for you.

Before we can give a correct answer, we need to have sample data in a consumable format. To learn how to post it, please read the article linked on my signature. Even if you're fine with your solution, that will help you to get better help in the future.:-)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

marcjason Mr or Mrs. 500 Points: 501 More actions · Answer 8

Hi,

Thanks for taking the time to help me. I cannot get the sample data to work using the code suggested in the link you posted. I get to the last step and it errors. I will try to explain the reason for using a cursor in the mean time whilst I work on getting the data up.

Each select query selects a table for a unique combination of the variables @ANLSID, @PERSPCODE, and @POLICYID. The variable @GROUPID is actually the same for all selects but I included it in the table ListPolicies_CB. I therefore think I need to use a cursor. I may be wrong. Here is the code. I appreciate that without the data it's quite difficult so I will work on getting it in the mean time.

DECLARE @GROUPID int

DECLARE @POLICYID int

DECLARE @ANSLID int

DECLARE @PERSPCODE varchar(2)

--Create a temporary table

CREATE TABLE #T1([GROUPID] int

,[POLICYID] int

,[EVENTID] int

,[RATE] float

,[PERSPVALUE] float

,[STDDEVC] float

,[STDDEVI] float

,[EXPVALUE] float

,[PERSPCODE] varchar(2))

--Declare the cursor

DECLARE myCursor CURSOR

FOR

SELECT GROUPID, POLICYID, [Analysis ID], PERSPCODE

FROM PC_Tool_Coding.dbo.ListPolicies_CB

OPEN myCursor

FETCH NEXT FROM myCursor

INTO @GROUPID, @POLICYID, @ANSLID, @PERSPCODE

WHILE @@FETCH_STATUS = 0

BEGIN

INSERT INTO #T1

SELECT@GROUPID GROUPID

, @POLICYID POLICYID

, rdm_port.EVENTID

, RATE

, SUM(PERSPVALUE) PERSPVALUE

, SUM(rdm_port.STDDEVC) STDDEVC

, SQRT(SUM(rdm_port.STDDEVI*rdm_port.STDDEVI)) STDDEVI

, SUM(EXPVALUE) EXPVALUE

, PERSPCODE

FROMPC_DIRECT_RDM_OCT13.dbo.rdm_port

LEFT JOIN PC_DIRECT_RDM_OCT13.dbo.rdm_anlsevent ON rdm_port.EVENTID = rdm_anlsevent.EVENTID

AND rdm_port.ANLSID = rdm_anlsevent.ANLSID

WHERErdm_port.ANLSID = @ANSLID

AND PERSPCODE IN (@PERSPCODE)

GROUP BY rdm_port.EVENTID

, RATE

, PERSPCODE

FETCH NEXT FROM myCursor

INTO @GROUPID, @POLICYID, @ANSLID, @PERSPCODE

END

CLOSE myCursor

DEALLOCATE myCursor

SELECT * FROM #T1

DROP TABLE #T1

Luis Cazares SSC Guru Points: 183706 More actions · Answer 9

This should give the expected results. However, it's no more than a shot in the dark.

Check the JOIN on PERSPCODE as I don't know to which table it belongs.

SELECT l.GROUPID,

l.POLICYID,

p.EVENTID,

RATE,

SUM(PERSPVALUE) PERSPVALUE,

SUM(p.STDDEVC) STDDEVC,

SQRT(SUM(p.STDDEVI * p.STDDEVI)) STDDEVI,

SUM(EXPVALUE) EXPVALUE,

l.PERSPCODE

FROM PC_DIRECT_RDM_OCT13.dbo.rdm_port p

LEFT JOIN PC_DIRECT_RDM_OCT13.dbo.rdm_anlsevent a ON p.EVENTID = a.EVENTID

AND p.ANLSID = a.ANLSID

JOIN (SELECT DISTINCT

GROUPID,

POLICYID,

[Analysis ID] ANSLID,

PERSPCODE

FROM PC_Tool_Coding.dbo.ListPolicies_CB) l ON p.ANLSID = l.ANSLID

AND a.PERSPCODE = l.PERSPCODE

GROUP BY l.GROUPID,