Querying for updated information
-
Is it possible for me to only pull the most recently updated or inserted information from a table? And if possible, take it a step further and have that information automatically copied to another table, deleting any existing info in the second table? Any help would be greatly appreciated. Thanks
-
You can use a INSTEAD OF trigger.
Regards,
gova -
You have to create an on update trigger to handle a task like that. Otherwise you'll have to buy a log reader to read the transaction log. Sql server doesn't keep anything else than that about modification dates.
-
I'm pretty sure someone asked this or a very similar question recently. I have not be able to find the thread.
If I recall one answer was to to select the max(id) from the first table and then copy to second table all rows where the where the id is <= @max_id. Then delete all rows from first table where id <= @max_id. You can then run the procedure again after a period of time.
-
That assumes that you have an identity column, a timestamp or a datetime field defaulting to getdate()
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Hi,
I would have to agree with an earlier response. The use of triggers as a means of auditing is quite simple. You can have and audit table that can store Updates, Deletes and Inserts on your tables....provides a pretty useful safety net in the event of an incorrect update etc.
I use them and have had no problems..
Watch out for the size of that table though
Regards..graeme
-
I remember that thread. It wasn't exactly the same problem. The guy wanted to TRANSFER the data to an audit table. So he had to do an insert, then delete the data he had transfered. But he was afraid that some new data was inserted between the 2 statements. So his solution was to save the last id of the records in the table. Transfer all the records that had an id <= than that record. Then he could delete the records that was <= than that id without being afraid of deleting unsaved data.
-
Remi,
Yes, that was the thread. Anyway, I thought I would post that idea just it case it may have been helpful to someone.
-
Always a good idea to send out new ideas
.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply