Identity column on the fly - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Identity column on the fly

Author

Topic

heze
Posting Yak Master

192 Posts

Posted - 2006-04-09 : 00:21:02

hi in books online we can create an identityi column for a table as follows:

SELECT IDENTITY(int, 1,1) AS ID_Num
INTO NewTable
FROM OldTable

Is there a way to do
SELECT IDENTITY(int, 1,1) AS ID_Num
FROM OldTable

ie without having to create a table and just to display the numeric value of the order of the respective record?

thank you

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-09 : 01:12:50

nopes Idenity function only works with the select into clause.

What are you doing to do ???

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

heze
Posting Yak Master

192 Posts

Posted - 2006-04-09 : 01:26:24

Ok Chira,I am trying to join two tables with no relationship whatsoever in a userdefined function
create fun f()
returns table
as
return
(
select IDENTITY(int, 0,1) AS ProposedID_Num,t1.*
from t1 inner join
(
select IDENTITY(int, 0,1) AS ProposedID_NumInner2,t2.*
from t2
) as t2 on (t1.ProposedID_Num=t2.ProposedID_NumInner2)
)

the idea is to create everything on the run and dont have to explicetely delete it after it is used

Do you think something can be done?

thank you

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-09 : 03:10:09

How about this solution..


create fun f()
returns table
as
return
(
select (Select Count(1) From T1 t Where t.[Pk]>=t1.[pk]) As ProposedID_NumInner1,t1.*
from t1 inner join
(
select (Select Count(1) From T2 t Where t.[Pk]>=t2.[pk]) As ProposedID_NumInner2,t2.*
from t2
) as t2 on (t1.ProposedID_Num=t2.ProposedID_NumInner2)
)

here Pk Means primary key .. if you have primary key in both the tables function then this query can be used..

Hope this helps

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-10 : 03:20:34

Where do you want to show the data?
Use Front end application to number the results

Madhivanan

Failing to plan is Planning to fail

heze
Posting Yak Master

192 Posts

Posted - 2006-04-10 : 10:28:36

mad,

The section of my script in which I have this case Is not part of the application, its in the middle of a +/- 500 line script and its just an ntermedaite step in the process, the ouptput is useless otherwise.

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources