Post reply

Insert Row Count

  • 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

  • August 21, 2006 at 8:38 am

    #655897

    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

  • August 21, 2006 at 8:42 am

    #655903

    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

  • August 23, 2006 at 12:23 am

    #656275

    you could use this example :

    select * into #temptable from [your_table]

    select @@ROWCOUNT row_count,* into #targettemp

    from #temptable

    select * from #targettemp

     

     

  • August 23, 2006 at 12:44 am

    #656278

    Also see this post http://qa.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.

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

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