Link to home
Start Free TrialLog in
Avatar of Nyana22
Nyana22Flag for Canada

asked on

row number without order by - TSQL

Hello,
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.
thanks
Avatar of BrandonGalderisi
BrandonGalderisi
Flag of United States of America image

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.
Avatar of Nyana22

ASKER

thanks,
can we do :

select into @table

@table is a variable table?
No.  Table variables must be declared ahead of time.

You can do:

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

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of BrandonGalderisi
BrandonGalderisi
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial