Post reply

count the number of data/rows entered

  • February 4, 2003 at 8:29 am

    #80687

    Hi,

    Our web-based application enters data into the SQL Server 7.0 DB. I don’t know how to count the number of data/rows entered by the application within a given time period.

    Any idea? Thanks!

  • February 4, 2003 at 8:37 am

    #447985

    Assume dtmInserted is a datetime field used to record when the application inserted the record...

    DECLARE @StartDate SMALLDATETIME

    DECLARE @EndDate SMALLDATETIME

    SET @StartDate = GETDATE() - 7

    SET @EndDate = GETDATE()

    SELECT COUNT(*) FROM table_name

    WHERE dtmInserted BETWEEN @StartDate AND @EndDate

    This sample script will give you the count of records added in the past 7 days...you can change the dates as you like...

  • February 4, 2003 at 12:28 pm

    #448023

    Thanks for your reply. The script works very well.

    But I have 250 tables, and the script just tells me the number of rows added within some day(s).

    Do you have any ideas about how to get the number of rows added to the whole DB within a given time period? or is there any statistical tools for this kind of purpose?

    Thanks

  • February 4, 2003 at 12:40 pm

    #448026

    quote:

    But I have 250 tables...

    The only thing I can think of is running a query from time to time summing the rows...

    try this...

    SELECT

    SUM(rows)

    FROM

    sysindexes

    INNER JOIN sysobjects

    ON sysindexes.id = sysobjects.id

    WHERE

    sysobjects.type = 'U'

    AND sysindexes.indid < 2

  • February 4, 2003 at 4:10 pm

    #448045

    Hmmm, If all your inserts are done through a Stored Procedure you could use the @@ROWCOUNT after each insert and update a logging table with the amount.

    another way would be to use the first script that jpipes has in a cursor loop and run that on a job and insert the results into a logging table.

    Gary Johnson

    DBA

    Sr. DB Engineer




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • February 4, 2003 at 4:42 pm

    #448048

    Great idea. I am going to try it.

    Thanks

  • February 6, 2003 at 2:53 pm

    #448295

    Hi,

    According to your instructions, I wrote a script to count the rows added within a given time period. The script works successfully. But I can't see the result.

    Would you tell me how to get the result of a

    stored procedure.

    Thanks a lot.

  • February 6, 2003 at 3:18 pm

    #448303

    There are a number of ways to return data from an SP, are you trying to return a record set (a number of rows), or a single value? Does the results need to be only displayed, or do they need to be passed back in a local variable, cursor, or a table?

    Gregory Larsen, DBA

    If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

    Gregory A. Larsen, MVP

  • February 10, 2003 at 9:15 am

    #448542

    It is enough to display a resord set ( A number of rows).

    Based on the discussions before, I wrote a SP, which uses a cursor loop, pass table name as a parameter, treat the SQL command as a string, and then running the string using EXECUTE sp_executesql @SQLString. However, I can't see the result.

    The following is part of the script,

    SET @SQLCommand = 'SELECT @TableRows = COUNT

    (*) FROM'

    SET @SQLCommand = @SQLCommand + @TableName

    SET @SQLCommand = @SQLCommand + 'WHERE

    ADD_DATE BETWEEN

    @StartDate AND @EndDate'

    SELECT @SQLString = CAST(@SQLCommand as

    NVARCHAR(300))

    EXECUTE sp_executesql @SQLString

    SELECT @TotalRows = @TotalRows + @TableRows

    PRINT @TotalRows

    Thank you for your consideration.

Viewing 9 posts - 1 through 8 (of 8 total)

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