Create new identity column within Select

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

SQL Server 2000 Forums

Transact-SQL (2000)

Create new identity column within Select

Author

Topic

dmcgiv
Starting Member

5 Posts

Posted - 2006-01-20 : 11:55:05

Hi,
is it possible to create a new identity column within a select statemet that is seperate from the table that is being queried?

Something like:

table Person(PersonId IDENTITY(1,1) NOT NULL, Name varchar(30) )

select
 NewPersonId, --some code here to indicate that it's an identity comuln
 Name
from 
 Person
where
 Name = 'name'

Kristen
Test

22859 Posts

Posted - 2006-01-20 : 12:13:52

Are you trying to get a Result Set with "row numbers"?

Or to insert some new data into the "Person" table and allocate then new identity numbers?

Or something else maybe?

Kristen

dmcgiv
Starting Member

5 Posts

Posted - 2006-01-20 : 12:15:30

I'm trying to get the row numbers 1,2,3,etc

nr
SQLTeam MVY

12543 Posts

Posted - 2006-01-20 : 12:16:36

You can insert into a temp table to get it
select
identity(int, 1, 1) as NewPersonId, --some code here to indicate that it's an identity comuln
Name
into #a
from
Person
where
Name = 'name'

select * from #a

or calculate it in the query - something like
select
NewPersonId = (select count(*) from Person t2 where t2.Name = 'name' and t2.PersonId <= t.PersonId) , --some code here to indicate that it's an identity comuln
Name
from
Person t
where
Name = 'name'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dmcgiv
Starting Member

5 Posts

Posted - 2006-01-20 : 16:09:00

I should have stated that the reason behine thie is that I don't want to use temp tables where possible

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-01-20 : 16:22:55

It is much easier for the presentation layer to assign row numbers to resultsets. Are you trying to come up with row numbers so that you can do paging, for calculations, or is it just for display purposes?

Also -- are you running SQL 2000 or 2005? SQL 2005 has some easy ways to do this in T-SQL, but not in 2000.

dmcgiv
Starting Member

5 Posts

Posted - 2006-01-20 : 16:54:48

I'm trying to improve the perf of my paging. I'm currently using a temp table but would prefere to use a derived table. I'm using SQL 2000.

Kristen
Test

22859 Posts

Posted - 2006-01-21 : 05:13:01

"I'm trying to improve the perf of my paging ... would prefere to use a derived table"

Jeff's Blogg is what you need then!

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Efficient+paging+of+recordsets

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-23 : 02:47:38

Also refer
http://www.aspfaq.com/show.asp?id=2120

Madhivanan

Failing to plan is Planning to fail