autoincrement - 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

General SQL Server Forums

New to SQL Server Programming

autoincrement

Author

Topic

heze
Posting Yak Master

192 Posts

Posted - 2006-09-20 : 17:50:25

hi,

does anybody know how to create an identity column in a query without having to use the into clause?

for example, i want to do the following:

select *,identity(int,0,1) as myId
from myTable

but this is not allowed

thank you

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-09-20 : 17:57:25

I think the normal consensus is to do this numbering in your front-end.

If you insist, though:
1. Create a table variable that mimics your source table but includes the myID identity col
2. Populate this using the source table
3. Do a select from this table variable.

I'm sure others will come up with more creative solutions....

HTH,

Tim

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-21 : 03:44:03

Select (Select count(*) from Tbl x where x.pk <= y.Pk) as myID, y.col2,
y.col3
from Tbl y
order by pk

Note: pk = Primary key column

Harsh Athalye
India.
"Nothing is Impossible"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-09-21 : 07:37:21

if you can't use a temp table then what's the point? it's the fastest and cleanest way if you really have
to do it on the server.
but do as timmy said: Front end is the place for this.

you do have a row_number() function in SQL server 2005 though.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

heze
Posting Yak Master

192 Posts

Posted - 2006-09-21 : 10:46:12

thanks for the responses, I finally solved the problem with cursors and forgot about the numbering
the problem with temp tables is when the procedure is accessed via a web page. If 2 users access the same proc at the same time and the proc produces a ## temp table one user will see an error, on the other hand, if I use a # table, its good for some situations but in this specific one Im also using dynamic sql, therefore I cant share the local# table within the dynamic code with the rest of the non dynamic code, in which case I would have to do all the procedure dynamic which makes the code difficult to handle----harsh_athalye's solution though, works great

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-09-21 : 11:06:28

> I finally solved the problem with cursors

this should never be the case.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

heze
Posting Yak Master

192 Posts

Posted - 2006-09-21 : 11:10:46

I would think because they are slow and inefficient and for this case the overhead may be too high considering solutions like Harsh's.., but when would you use cursors then?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-21 : 11:17:51

>>I cant share the local# table within the dynamic code with the rest of the non dynamic code
You can "share" the # table with dynamic code if the table exists in the code that execs the dynamic sql:


set nocount on
create table #t (rowid int identity(1,1), i int)

insert #t (i)
exec ('select 10')
exec ('insert #t (i) select 20')
exec ('insert #t (i) select i from #t')

select * from #t
drop table #t

output:
rowid       i           
----------- ----------- 
1           10
2           20
3           10
4           20

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-21 : 11:30:04

>>but when would you use cursors then?
The last time I used a cursor was to prove the point of how slow they were and how unfriendly they were in regards to concurrent requests (locking/blocking). That is the best reason to use a cursor :)

I use one occasionally to generate some test data or something like that. I don't have any implemented in any code that is called by an application. I think I used one once in a (one-time) process to import a complex set of related financial information. I didn't care about locking resources or (speed) performance. It was a rare case where for each customer there was a tremendous amount of related data to be inserted and business rules that needed to be honored. It could have been done with an entirely set based approach but it was easier to understand deal with exceptions this way.

Be One with the Optimizer
TG

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-21 : 12:13:13

quote:
Originally posted by heze

thanks for the responses, I finally solved the problem with cursors and forgot about the numbering
the problem with temp tables is when the procedure is accessed via a web page. If 2 users access the same proc at the same time and the proc produces a ## temp table one user will see an error, on the other hand, if I use a # table, its good for some situations but in this specific one Im also using dynamic sql, therefore I cant share the local# table within the dynamic code with the rest of the non dynamic code, in which case I would have to do all the procedure dynamic which makes the code difficult to handle----harsh_athalye's solution though, works great

You should not use a ## temp table.

CODO ERGO SUM

heze
Posting Yak Master

192 Posts

Posted - 2006-09-21 : 13:07:55

great help TG,MVJ, thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources