Insert Row Count

Question

Insert Row Count

Richard Sabbara

Ten Centuries

Points: 1382
More actions
August 21, 2006 at 8:10 am

#114091

I have a standard select statment and I need to add a column that counts the rows, so for row one it will the column will have the value of 1, row two will have the value 2 and so on.
Any suggestions?
Richard

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Vladan SSC-Insane Points: 22072 More actions · Answer 1

Hello,

just to make clear what you need - post header says "Insert row count", but actually you seem to be asking for a way to SELECT (not insert) row number (not row count) for each row returned in some query... did I understand you correctly?

SQL Server 2005 has a function that does that; in 2000 you have to find a workaround. Well, it depends on how you use the result of the query, but one obvious way would be to insert the result into temporary table with IDENTITY column - especially if you plan to work with these row numbers in some way later (e.g. display first 20 rows, on demand display next 20 rows without re-running the query). Or just return the resultset from SQL as it is and number the rows "outside", in a front-end (presentation software or whetever you are using).

HTH, Vladan

Out For Justice SSCrazy Points: 2733 More actions · Answer 2

You could use a trigger to get the row count e.g.

CREATE TABLE [dbo].[RowCounts] (

[MyID] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,

[Counter] [int] NULL

) ON [PRIMARY]

GO

CREATE TRIGGER [CountRows] ON [dbo].[RowCounts]

FOR INSERT

AS

UPDATE RowCounts

SET Counter = spam.RecordsInserted

FROM (SELECT Count(*) as RecordsInserted FROM Inserted) AS spam, Inserted

WHERE RowCounts.MyID = Inserted.MyID

Then use the following to populate it:

insert into Rowcounts (MyID)

select distinct name from sysobjects

insert into Rowcounts (MyID)

select distinct name from syscolumns

select * from rowcounts

Ivan Rakovic Valued Member Points: 69 More actions · Answer 3

you could use this example :

select * into #temptable from [your_table]

select @@ROWCOUNT row_count,* into #targettemp

from #temptable

select * from #targettemp

Vladan SSC-Insane Points: 22072 More actions · Answer 4

Also see this post http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=302916 for a similar problem - even more complicated, because the requirement is not to number all rows from 1 to n, but to start numbering from 1 for each distinct value in a certain column.