June 22, 2010 at 1:37 am
I am having a database. i wanna to know the recent records from a table. table name may be anything in that database. means consider that db having table1 and table2. if i insert a records from table2. it should say the most records records inserted in table2 and also that record's id. which is possible?
June 22, 2010 at 1:47 am
You'll need date inserted column or identity on each table. A column that shows when a row was inserted.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 22, 2010 at 1:55 am
June 22, 2010 at 3:40 am
I am having those fields in my table.
consider the table having the following columns,
ID int identity(1,1) PK,
Name nvarchar(25)
named as table1 and the same fields and some additional fields also there in table2.
now i am inserting the record in table2. so that from the log itself i need to find recent records inserted in table2.
June 22, 2010 at 4:08 am
Dear karthik, there is no log to do this job. What Gail has mentioned is that if you have an identity column, you can probably query like 'select top 10 * from table order by id desc'.
If you have a timestamp, you can query like 'select * from table where timestamp > dateadd(d,-1,getdate())'
Use sp_msforeachtable if you want this to be done for each table.
Please validate my answer with someone more technically sound.
- arjun
https://sqlroadie.com/
June 22, 2010 at 4:17 am
I too feel What Arjun suggests is correct. Timestamp will be modified whenever the row is updated. so the row containing the latest timestamp will be the recently modified\updated row. One catch here, even if the row is updated , the timestamp is updated but our requirement is to have the latest inserted record.
Kindest Regards,
Paarthasarathy
Microsoft Certified Technology Specialist
http://paarthasarathyk.blogspot.com
June 22, 2010 at 4:21 am
Arjun Sivadasan (6/22/2010)
If you have a timestamp, you can query like 'select * from table where timestamp > dateadd(d,-1,getdate())'
If you're talking about the timestamp data type, it is not a date and cannot be converted to a date. If you're talking about a datetime column that defaults to getdate (hence putting the date inserted into that column), that's fine.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 22, 2010 at 4:23 am
Create two columns - Time_of_Creation, Time_of_Updation and fill them accordingly.
- arjun
https://sqlroadie.com/
June 22, 2010 at 4:27 am
GilaMonster (6/22/2010)
Arjun Sivadasan (6/22/2010)
If you have a timestamp, you can query like 'select * from table where timestamp > dateadd(d,-1,getdate())'If you're talking about the timestamp data type, it is not a date and cannot be converted to a date. If you're talking about a datetime column that defaults to getdate (hence putting the date inserted into that column), that's fine.
Ya that's what I meant Gail. Timestamp err.. sorry for the confusion.
@karthik - If you are to create two columns indeed, then don't give a default value for Time_of_Creation, as you want it to be filled only the first time.
- arjun
https://sqlroadie.com/
June 22, 2010 at 4:31 am
Arjun Sivadasan (6/22/2010)
@Karthik - If you are to create two columns indeed, then don't give a default value for Time_of_Creation, as you want it to be filled only the first time.
Why not? Surely a default is exactly what you want.
If you don't put a default, you'll have to remember to set the date of creation either in the insert query or in a trigger. Much easier to set a default of getdate on the date created column and let SQL handle that, then either remember to set the value for the DateUpdated or use a trigger for it (assuming date updated is necessary, the original post just asked for date inserted)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 22, 2010 at 4:40 am
Ya right, I was confused there. What I meant was if karthik needs to identify inserted rows and updated rows separately, he needs two columns - Time_of_Creation and Time_of_Updation. And he will have to fill the Time_of_Creation column the first time, and the Time_of_Updation column the subsequent times.
Default value of getdate() can be mentioned. Thanks for pointing that out Gail.
- arjun
https://sqlroadie.com/
June 22, 2010 at 5:50 am
Thanks for your reply guys. but what i need is, i dont know the tablename in the database. means that db having more no.of tables. so from those tables i need to know, which table's records is getting inserted recently. i need to track this.
ie. for past 1 hour what are all the table's records are affected in my database. it may be insert,update or delete.
June 22, 2010 at 5:56 am
Karthik Palanivel (6/22/2010)
ie. for past 1 hour what are all the table's records are affected in my database. it may be insert,update or delete.
There is no automatic way to do this. If you need to track all insert/update/delete, you need to create triggers on all tables to populate an audit table.
Pity this isn't SQL 2008, or you could use change tracking or change data capture. However neither is on SQL 2005.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 22, 2010 at 11:54 pm
Isnt there a way to check any statistics to find the table that is frequently updated?
Kindest Regards,
Paarthasarathy
Microsoft Certified Technology Specialist
http://paarthasarathyk.blogspot.com
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply