August 1, 2014 at 8:08 am
Hi All,
Just for the curiosity, I want to ask . Is there any way to check how many records has been updated in the particular tables.
For Example, One Employee and Department table is there and I will create one job that will be updated department table according to the Employee table table.
Suppose Employee is moved to the other project with different Manager, so if job will be executed next day it will update the Employee details.
If I want to know how many Employee has been updated today, so how can I check. Is there anything to find out the count of only updated records.
Thanks in advance
August 1, 2014 at 10:19 am
The best way to do this is to add a modified datetime column to the tables and either update your process or use an update trigger to update the column when the record is updated. Then it is a simple query to tell how many records have been changed.
August 1, 2014 at 11:09 am
As Keith mentioned, there is no way to do this directly without having something setup. You can use a trigger and audit changes into another table (or the same one as noted above). You could implement CDC or CT (not recommended here), or you could have some eventing or traces running (again, not recommended necessarily).
Can I ask what you are trying to accomplish? I know you are looking for updated records, but why? Perhaps there is a better solution?
August 1, 2014 at 11:57 am
Using a trigger is probably the most straight forward approach, if you add a table holding the primary key value and a date, then you can use the output clause to insert into that table when ever a row is updated, that would be when it exists in both inserted and deleted pseudo tables. This has a lot less overhead than updating a column in the actual table and can potentially be used for more than one table. If you want to keep the history of the values then that can be done using FOR XML. Have a look at this thread for more information about the trigger implementation.
😎
August 1, 2014 at 4:26 pm
Hi
CDC can can work as well as trigger. If you want to create like a manual system I recommend using something like this
Create a Change table in your database lets call it dbo.Changes
in the script that updates/insert and something like this.
declare @count int
-- Update the records you have
update <UpdateTable>
seta.<ColumnName> = b.<ColumnName>
from <UpdateTable>a
join <InfoTable> b with (nolock) on
join join join
set @count = @@ROWCOUNT
insert into dbo.Changes
select 'Updates',Getdate(),@count
------------------------------------------------------------
declare @count int
-- insert those that you don't have
insert into <InsertTable>
select <columns>
from <TableName> with (nolock)
join join join
set @count = @@ROWCOUNT
insert into dbo.Changes
select 'Inserts',Getdate(),@count
Hope this gives you one more option to choose from.
--------------------------------------------------------------------------------------------------------------------------------------------------------
To some it may look best but to others it might be the worst, but then again to some it might be the worst but to others the best.
http://www.sql-sa.co.za
August 2, 2014 at 2:02 pm
CDC works great, but it complicates administration, and especially DR. If you use it, make sure you understand how to restore it.
August 2, 2014 at 2:25 pm
Also need to keep in mind that CDC is Enterprise Only.
p.s. Nolock has side effects, it's not a go-faster switch that should be added to every single statement. Let's not teach people bad habits.
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
August 2, 2014 at 3:18 pm
GilaMonster (8/2/2014)
Also need to keep in mind that CDC is Enterprise Only.p.s. Nolock has side effects, it's not a go-faster switch that should be added to every single statement. Let's not teach people bad habits.
Further on Gail's comment on nolock, according to MSDN: For UPDATE or DELETE statements: This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
😎
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply