Convert a cursor to a non cursor query

I have the following cursor that I want to avoid and have it some how translated/converted into a transact SQL query (that should be without cursor). Can someone please help:
DECLARE @RegionID INT, @RegionCD VARCHAR(15), @CountryID INT


DECLARE CountryRegion_cursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR 
		SELECT RegionID, RegionCD FROM Regions
		OPEN CountryRegion_cursor
		FETCH NEXT FROM CountryRegion_cursor INTO @RegionID, @RegionCD
		WHILE (@@fetch_status=0)
		BEGIN

		INSERT INTO RegionCountryHierarchy(RegionCountryID, RegionCountryNM, RegionCountryFlag)
		VALUES(@RegionID, @RegionCD, 'R')

		INSERT INTO RegionCountryHierarchy(RegionCountryID, RegionCountryNM, RegionCountryFlag)
		SELECT CountryID, CountryNM, 'C' FROM Countries
		WHERE RegionID=@RegionID
		
		FETCH NEXT FROM CountryRegion_cursor INTO @RegionID, @RegionCD
		END
		CLOSE CountryRegion_cursor
		DEALLOCATE CountryRegion_cursor

Open in new window

LVL 1
skaleem1Asked:
Who is Participating?
 
_agx_Commented:
> The cursor first returns the region and then any contained countries

See below
SELECT    t.RegionCountryID, t.RegionCountryNM, t.RegionCountryFlag
FROM    (
        SELECT  RegionCD AS ParentRegion, RegionID AS RegionCountryID, RegionCD AS RegionCountryNM, 'R' AS RegionCountryFlag
        FROM    Regions
        UNION ALL
        SELECT  r.RegionCD AS ParentRegion, CountryID, CountryNM, 'C' AS RegionCountryFlag
        FROM    countries c INNER JOIN @Regions r ON c.RegionID = r.RegionID
        ) t
ORDER BY t.ParentRegion, 
         CASE WHEN t.RegionCountryFlag = 'R' THEN 1 ELSE 2 END,    
         t.RegionCountryNM

Open in new window

0
 
chapmandewCommented:
INSERT INTO RegionCountryHierarchy(RegionCountryID, RegionCountryNM, RegionCountryFlag)
SELECT RegionID, RegionCD, 'R' FROM Regions
union all
SELECT c.CountryID, c.CountryNM, 'C' FROM Countries c
join Regions r on c.RegionID = r.RegionID
0
 
skaleem1Author Commented:
The cursor first returns the region and then any contained countries and so on and so forth (please see the attached screen shot). Your query first returns all the regions and then all the countries (which is not what is desired and that is why I used the cursor at the first place). Any modification to the query to get the required results?
CursorResultset.GIF
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
chapmandewCommented:
the outcome is exactly the same as the cursor (your cursor doesn't return data).

if you want it to display as needed,  you'll need to use an ORDER BY clause in your SELECT statement.
0
 
skaleem1Author Commented:
Where can I put the order by clause in your query to return the data as expected?
0
 
chapmandewCommented:
Show me the query that you use to return the data...the cursor doesn't return data.
0
 
ralmadaCommented:
Honestly you should consider adding an extra column to your RegionCountryHierarchy table, just containing the RegionID, then do the insert as follow and finally add the sort criteria in your select query, the order in the insert shouldn't really matter.

INSERT INTO RegionCountryHierarchy(RegionID, RegionCountryID, RegionCountryNM, RegionCountryFlag)
SELECT RegionID, RegionID, RegionCD, 'R' as Flag FROM Regions
union all
SELECT RegionID, CountryID, CountryNM, 'C' FROM Countries

select * from RegionCountryHierarchy 
order by RegionID, RegionCountryFlag Desc

Open in new window

0
 
_agx_Commented:
> @Regions

   Just update with your real table names ...
0
 
ralmadaCommented:
I would recommend doing that in the insert as I've mentioned in my previous comment http:#a30928028. However, if you insist then my approach will be like this:
INSERT INTO RegionCountryHierarchy(RegionCountryID, RegionCountryNM, RegionCountryFlag) 
select RegionCountryID, RegionCD, Flag
from
(
	SELECT RegionID, RegionID as regionCountrID, RegionCD, 'R' as Flag FROM Regions 
	union all 
	SELECT RegionID, CountryID, CountryNM, 'C' FROM Countries 
) a
order by RegionID, RegionCountryFlag Desc

Open in new window

0
 
_agx_Commented:
@ralmada - I don't think that would order correctly. I believe they want it ordered by name (not id) with _each_ regions listed first, and the countries in that region directly beneath it.
0
 
chapmandewCommented:
hold on here...the original question had NOTHING to do w/ ordering.  The question was how to take the cursor code and implement it as a single statement.
0
 
skaleem1Author Commented:
ralmada and aqx,

Both of your solutions work and I have picked this as the final one (however I will split the points between the two to be fair)

Thanks a lot


INSERT INTO RegionCountryHierarchy(RegionCountryID, RegionCountryNM, RegionCountryFlag)  
SELECT RegionCountryID, RegionCD, RegionCountryFlag
FROM
(
        SELECT RegionID, RegionID as RegionCountryID, RegionCD, 'R' AS RegionCountryFlag FROM Regions  
        UNION ALL  
        SELECT RegionID, CountryID, CountryNM, 'C' AS RegionCountryFlag FROM Countries  
) a
ORDER BY RegionID, RegionCountryFlag DESC
0
 
_agx_Commented:
Well.. I don't know. I think in this case it happens to be part and parsel of what the cursor code is doing. Since it determines what the results in the table are at the end of the cursor.
0
 
_agx_Commented:
> Both of your solutions work

Just curious, but how is ralmada's suggestion different than what chapmandew suggested earlier (with an order by)? Since you said chapmandew's suggestion didn't work.
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.