row number without order by - TSQL

Posted on 2009-04-06
Medium Priority
Last Modified: 2012-05-06
I have one excel sheet with some data.
I am getting the data to Sql Server 2005 by the openrowset function.
I am writing a table valued function that return the data from that excel sheet.
I want that the first field returned to be simply the row number and the other fields will be those of the excel file.
I can't sort by any of the excel field, because the table valued function should return the data in the exact same order as they were in the excel.
Question by:Nyana22
  • 3
  • 2
LVL 39

Expert Comment

ID: 24080114
You have no guarantee of order unless you explicitly define one.  But one thing that you can do is to load the data into a #temp table that has an identity column.

Author Comment

ID: 24080192
can we do :

select into @table

@table is a variable table?
LVL 39

Expert Comment

ID: 24080254
No.  Table variables must be declared ahead of time.

You can do:

select ident=identity(int,1,1),* into #Temp
From <EXCEL>

Author Comment

ID: 24080302
Msg 2772, Level 16, State 1, Procedure entreeSysteme, Line 37
Cannot access temporary tables from within a function.

can i do :
select ident=identity(int,1,1),* into #Temp
From <EXCEL>

in a table valued function
LVL 39

Accepted Solution

BrandonGalderisi earned 2000 total points
ID: 24080355
No you can't.  If you have a function UDF that gets you the data....

select ident=identity(int,1,1),yf.*
into #Temp
From dbo.YourFunction() YF

But UDF's MAY not work for that.  I don't know because i've never tried.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question