none
SQL Query to find the no of records in a daily basis

    Question

  • Hi..!!

    I have a table that update on a daily basis and i need to pull the newly update record on a daily basis.

    EMP Name

    A1  SQLGuy

    B2  MSTech

    A3  IT

    C1  Admin

    Kindly advice as in there is no specific column to sort or identify the newly add record in table on a daily basis

    Thanks and Regards,
    Anirban Singha

    Saturday, October 12, 2013 6:25 AM

Answers

  • Thanks Saeid,

    The table is very sensitive and do not want to do any alteration on it. Is there is a other way to get this done.

    Regards,
    Anirban Singha

    You said that:

            "there is no specific column to sort or identify the newly add record in table"


    So I suggest to use CHANGE TRACKING to tracking access and changes to your data, because of its minimal change in your table (it's very little change!!!).

    Before introducing SQL Server 2008, there were some mess approaches like using:

    • triggers
    • triggers + trace + dmv views

    etc.

    There are new techs in SQL Server 2008 and above versions that target tracking access or changes, such as:

    • Audit
    • Change Tracking
    • Change Data Capture
    • Extended Events

    These techs are awesome because of simplicity to start and usage. The best approach _that I tested within a corporate sync system about three years ago_ is Change Tracking for your situation.

    With Change Tracking it's very easy to get started. You can dive in it simply like these codes:

    ALTER DATABASE AdventureWorks2012
    SET CHANGE_TRACKING = ON
    (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
    go
    
    ALTER TABLE Person.Contact
    ENABLE CHANGE_TRACKING
    WITH (TRACK_COLUMNS_UPDATED = ON) ;
    
    go

    For more info, have a look at these two links:

    Enable and Disable Change Tracking (SQL Server)

    Work with Change Tracking (SQL Server)


    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012


    Saturday, October 12, 2013 3:53 PM

All replies

  • You can enable CHANGE TRACKING in table level if you use SQl SERVER 2008 and above versions. For more info see this link:

    Using Change Tracking in SQL Server 2008


    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    Saturday, October 12, 2013 7:14 AM
  • Thanks Saeid,

    The table is very sensitive and do not want to do any alteration on it. Is there is a other way to get this done.

    Regards,
    Anirban Singha

    Saturday, October 12, 2013 3:01 PM
  • Thanks Saeid,

    The table is very sensitive and do not want to do any alteration on it. Is there is a other way to get this done.

    Regards,
    Anirban Singha

    Hi,

    If the table has created_date & lastmodified_date columns then we can track easily using datetime stamp without creating audit trigger or CDC .


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Saturday, October 12, 2013 3:10 PM
  • Thanks Saeid,

    The table is very sensitive and do not want to do any alteration on it. Is there is a other way to get this done.

    Regards,
    Anirban Singha

    You said that:

            "there is no specific column to sort or identify the newly add record in table"


    So I suggest to use CHANGE TRACKING to tracking access and changes to your data, because of its minimal change in your table (it's very little change!!!).

    Before introducing SQL Server 2008, there were some mess approaches like using:

    • triggers
    • triggers + trace + dmv views

    etc.

    There are new techs in SQL Server 2008 and above versions that target tracking access or changes, such as:

    • Audit
    • Change Tracking
    • Change Data Capture
    • Extended Events

    These techs are awesome because of simplicity to start and usage. The best approach _that I tested within a corporate sync system about three years ago_ is Change Tracking for your situation.

    With Change Tracking it's very easy to get started. You can dive in it simply like these codes:

    ALTER DATABASE AdventureWorks2012
    SET CHANGE_TRACKING = ON
    (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
    go
    
    ALTER TABLE Person.Contact
    ENABLE CHANGE_TRACKING
    WITH (TRACK_COLUMNS_UPDATED = ON) ;
    
    go

    For more info, have a look at these two links:

    Enable and Disable Change Tracking (SQL Server)

    Work with Change Tracking (SQL Server)


    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012


    Saturday, October 12, 2013 3:53 PM