Saturday, February 25, 2012

Convert Oracle "connect by level < 10" into MS SQL server 2005

I want to convert oracle "SELECT LEVEL R FROM CONNECT BY LEVEL <=10" my bottom requirement is get 1 to 10 as a dynamic table inside a query.

Ex: Select id, name, R from names N, (1,2,3,4,5,6,7,8,9,10) R WHERE id < 1000

If any one know something regarding this please reply me.

Thx,

Buddhika

We have recursion in SQL Server, but are you asking about the CONNECT BY LEVEL auto-numbering use?

Buck Woody

|||

If you're looking for the hierarchical query in SQL Server, you can check here:

http://www.databasejournal.com/features/oracle/article.php/3552521

Buck Woody

|||MSDN says there is no SQL equivalent to CONNECT BY:
http://www.microsoft.com/technet/prodtechnol/sql/70/proddocs/admincmp/75517c11.mspx?mfr=true

It suggests using a stored procedure instead.
|||That reference is for the SQL 7 docs (almost 10 years old). IIRC Connect by with level is recursion with an automatic recursion depth checker/autonumber. Start with Bucks link.

No comments:

Post a Comment