?
Solved

row number without order by - TSQL

Posted on 2009-04-06
5
Medium Priority
?
825 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 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.
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

762 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