Query to find the records from a table which are updated or added in the last one month ?

Question

Hi,

I want to select the records from a table which are updated or added in the last one month (table doesn’t have any datetime field and I can’t change the table structure). Is it possible to write a sql query for that? how to fetch those records from table.

regards, kumar

Answer 1

No It is not possible. You need to keep a track of that by adding the date fields to the structure of the table. we cannot do as you said.

Answer 2

It is possible if you are allowed to use triggers and add extra tracking tables(whilst leaving the existing table structure untouched)

Obviously you would not be able to find out what has already changed but this would enable you to come up with a solution to track changes made once implemented.

If this is useful then let me know and I can give you more details.

Answer 3

If you are using SQL Server 2008 (or have the ability to upgrade), you could have a look at using Change Data Capture to track when changes are made to your tables. This solution would not involve changing existing tables, but you would be making a database-level change.

If upgrading to 2008 is not an option, you can create a set of change tracking tables and use triggers to maintain them when updates are made.

Answer 4

If you can't ;

1. Modify the db to add a timestamp col.
2. Add another table that acts as an audit log, fed from a trigger on the original table

Then you'll need to write an ssis package that, at the end of every day compares the table in question to a copy of the table (that you took the previous day). You then log any differences in a new audit table.

Answer 5

It's not possible by querying that table.

You can get that records by verifying the transaction logs in Oracle. But i am not sure in Sql Serevr.