Idenity with in a View - 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

Transact-SQL (2000)

Idenity with in a View

Author

Topic

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2008-09-11 : 12:27:57

Good afternoon

Is there a way to insert an ID column into a (view)
which will auto-number each line based on a starting number? It would be awesome if there is a way to do this in a view.
If so please let me know how

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-11 : 12:43:43

If you were using SQL Server 2005, this would be very easy to add to it.

But you can't add an identity value to a view.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2008-09-11 : 13:22:11

Hi tkizer

Thanks for the responce, understood.. I cannot add an identity value to a view.
Question, then is it possible to add a column to a view and auto-number each line based
on a starting number? for instance, say I started the line numbering at 50, then
the next line would be 51, 52 and so on.

Would this be possible? as I only need to number each line in the view. It dosen't have
to be an Identity

If this is possible, please let me know

Thanks
Again

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-11 : 13:37:09

The easiest way to do it would be to put the view into a temp table and add an identity column to that table.

SELECT IDENTITY(int, 1, 1) AS ViewID, ColumnA, ColumnB, ColumnC, ...
INTO #Temp
FROM YourView

SELECT *
FROM #Temp
WHERE ...

But the better solution is just to number the rows using application code, since this is most likely a presentation issue.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2008-09-11 : 13:55:41

Hi tkizer

Once again thanks for the responce.. understood.
given this i'll just go the suggested Temp table route.

Question pertaining to the Temp table with an identity column.
Would something like this work for auto-numbering

INSERT INTO Temp (MyID Int IDENTITY(-1000000, 100) NOT NULL
,MyDescription NVarChar(50) NOT NULL)

select * from Temp
GO

Someone here told me this would work, but I find getting solutions from yourself
and others on this site has worked the best for me, so I don't entirely trust
the solution noted above and thought i'd run it by the experts (you guys & gals)

E

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-11 : 14:13:04

Yes you can do it that way too, although with slight syntax changes.

You'll need to get familiar with SET IDENTITY_INSERT option though.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources