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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
_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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.