Select with RowID - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Select with RowID

Author

Topic

apantig
Posting Yak Master

104 Posts

Posted - 2004-03-23 : 21:29:07

How can I load records using "Select" with RowID column?

Example Table:

EmpName
-------
Jason
John
Nick
Mark

Output result:



RowID EmpName
  1   Jason
  2   John
  3   Nick
  4   Mark

RowID is not included in the table structure. I just need to know on how to load records in setbased method with incremental value of RowID column.

Thank you very much.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-03-23 : 21:38:39

Search SQL Team for "row number", you'll find a number of articles that have various solutions.

SamC
White Water Yakist

3467 Posts

Posted - 2004-03-23 : 21:44:05

I'm confused about where RowID is generated in your example.

If you want to return a record by RowID, it's got to be a column in the Table. There are no intrinsic RowIDs.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-23 : 22:05:27

presentation layer. it is trivial to implement in almost any presentation app.

- Jeff

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-23 : 22:20:37

quote:
Originally posted by jsmith8858

presentation layer. it is trivial to implement in almost any presentation app.

While this can be implemented in another layer, I believe that since it requires a cursory approach and more code (ala. ASP.NET - datagrid, ItemDataBound events or TemplateColumns) to perform what can be a simple SELECT INTO at the DB, it makes sense to perform this in the data access layer. IMHO

mateenmohd
Constraint Violating Yak Guru

297 Posts

Posted - 2004-03-23 : 23:27:13

I think you need to include rowid colume in the table.
and click identity option yes
it will automatic increment the value when record insert.

quote:
Originally posted by apantig

How can I load records using "Select" with RowID column?

Example Table:

EmpName
-------
Jason
John
Nick
Mark

Output result:

RowID EmpName
1 Jason
2 John
3 Nick
4 Mark

RowID is not included in the table structure. I just need to know on how to load records in setbased method with incremental value of RowID column.

Thank you very much.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-03-23 : 23:36:58

No, you don't need to include it in the table.

The solution the others were alluding to is this :

SELECT Identity(int,1,1) as ids, *

INTO #foo
FROM foo
ORDER BY EmpName

SELECT * FROM #foo

DROP Table #Foo

Damian

apantig
Posting Yak Master

104 Posts

Posted - 2004-03-24 : 03:43:58

Merkin,

Hi. I tried your suggestion and it really works. I never thought "SELECT Identity(int,1,1) as ids" is possible in that statement.

Thank you very-very much, you are all a big help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources