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.

5 Answers
5

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.

thanx for u r suggestion. but we not using any kinds triggers Is there any other scenarios that we can do it ?
–
kumarApr 21 '10 at 10:41

1

In that case it isn't possible to do what you want to do, you can't magic data out of thin air. If you were in SQL 2008 you may be able to enable change tracking but that is the last shred of hope for this.
–
David SteeleApr 21 '10 at 11:03

I can't magic the data out in air. but i will try .In that case is it possible to use getDate() or sys.objetcs or @@IDENTITY to get the records from table which r updated in last one month can i use any them or all?
–
kumarApr 21 '10 at 12:20

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.