Adding identity to SQL view

[TJones8]

Hi, I'm running SQL 2000.
I have a view that needs to have basically the same as an table identity auto-matic incrementing field.
I have tried using the quick-but-nasty Count(*) method, but that doesn't work since my view is based on many tables.

Also, I cannot import the data from the view into a new table because, basically, i've been told not to.

I hate taking on other ppl's projects :-(

So, does anyone know of a way i can add a neat little simple auto-incrementing field to a view?? it only needs to be seeded at 1 and incremented by 1.

TIA

TJ

if at first you don't succeed, get a 10lb lump hammer

 

[JamesLean]

There's no easy way of doing this in a view. There is an IDENITITY() function but this is only valid when using SELECT INTO.

Does it have to be an incrementing value? If not you could use the NEWID() function to generate a unique value for each row.

--James

 

[NoCoolHandle]

Can you use UserDefined Functions?

 

[TJones8]

I was looking throught the help section and came accross IDENTITY(), i yey'd with joy.
Then i saw it could only be used to SELECT...INTO, and all was not well.

I'll give NEWID() a try - but i'll have to check if it has to be incremental or just unique...

And i can mess with user defined functions.

Thx

if at first you don't succeed, get a 10lb lump hammer

 

[TJones8]

Well, that was short-levied... NEWID() is out, since the sequence number has a fixed-width of 8 chars...

argh!!

if at first you don't succeed, get a 10lb lump hammer