September 13, 2009 at 8:00 am
Mazharuddin Ehsan (9/11/2009)
Let us say I have a flat file containg rows of data and I want to uplaod the file to a database table with an additional column comprising of the line number as it is appearing in the text file.
:blink: ok, lets go back to the basics.
#1... Poster wanted to know how to replicate Oracle's "rownum" functionality in SQL Server - lets forget for a minute "rownum" doesn't work the way Poster thinks it work.
#2... Since "rownum" is nothing but a pseudo-column populated at the time rows are made available to the user as a result of a "select" statement... don't you think it's a little over reaching the load-a-text-file scenario? 😀
I feel like this is one of those cases where people try to justify anything resorting to no-matter-how-weird-and-far-away-from-reality justification - like changing the original post just to make fit an answer 😉
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 14, 2009 at 10:07 am
PaulB (9/13/2009)
Mazharuddin Ehsan (9/11/2009)
Let us say I have a flat file containg rows of data and I want to uplaod the file to a database table with an additional column comprising of the line number as it is appearing in the text file.:blink: ok, lets go back to the basics.
#1... Poster wanted to know how to replicate Oracle's "rownum" functionality in SQL Server - lets forget for a minute "rownum" doesn't work the way Poster thinks it work.
#2... Since "rownum" is nothing but a pseudo-column populated at the time rows are made available to the user as a result of a "select" statement... don't you think it's a little over reaching the load-a-text-file scenario? 😀
I feel like this is one of those cases where people try to justify anything resorting to no-matter-how-weird-and-far-away-from-reality justification - like changing the original post just to make fit an answer 😉
At least now we know what is rownum at last.:-)
More seriously,
1. Actually rownum as it works in Oracle is not required at all in SQL Server as there are many numbering functions available like row_number(), rank() and "TOP" clause. As some one said earlier in this thread 'usage is the key', there is nothing that 'cannot' be solved using these features about dynamically number rows in a SELECT Transact-SQL statement.
In fact one of the practical usage of rownum in Oracle is Top-N Query.
2. If we are really required to create an emulation of rownum 'as it is' in Oracle, for some reason then also it can be done for sure, I beleive in SQl Server. I just gave a quick example. Some people in this thread pointed to some inhibitions in my solution. I can cement it to produce same guarented result as rownum if really required. May be I will not do it for free now 😀
Thanks
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
September 14, 2009 at 10:33 am
Mazharuddin Ehsan (9/14/2009)Actually rownum as it works in Oracle is not required at all in SQL Server
yes... Yes... YES! you got my vote for President! 😀
Not to mention original poster expected rownum to do in Oracle something totally different at what it actually does. Go figure.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 3 posts - 31 through 32 (of 32 total)
You must be logged in to reply to this topic. Login to reply