Solved

row number without order by - TSQL

Posted on 2009-04-06
5
814 Views
Last Modified: 2012-05-06
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
0
Comment
Question by:Nyana22
  • 3
  • 2
5 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
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.
0
 

Author Comment

by:Nyana22
ID: 24080192
thanks,
can we do :

select into @table

@table is a variable table?
0
 
LVL 39

Expert Comment

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

You can do:

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

Author Comment

by:Nyana22
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
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 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.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Need some help wiht :CAST AS Double 11 44
insert query with value having 's 2 47
Grid querry results 41 72
SQL Query with Sum and Detail rows 2 40
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now