Tuesday 25 April 2017

Select what are the new values has been updated in a table on a particular column :

Step 1 : create table
create table Auto_track(ID int unique identity(1,1),
Name varchar(50) default 'Reza',Value int)
Step 2 : Inserting Records with Default values
insert into Auto_track values(default,null)
go 10
Select * from Auto_track

Notes : Must be a primary key in your table
alter table Auto_track add primary key(ID)
Step 3 : Enable database tracking mode for a specific periods
ALTER DATABASE [YOUR_DB]
SET CHANGE_TRACKING = ON  
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)  
Step 4 :Enable Table tracking mode
ALTER TABLE [dbo].[Auto_track]  
ENABLE CHANGE_TRACKING  
WITH (TRACK_COLUMNS_UPDATED = ON)  
Step 5 :Update value column with some values
update Auto_track set Value=ID+100
Select * from Auto_track

Step 6 :Execute the below code will show the modified records
SELECT ISNUll(pn.Value,0) as Value 
from changetable(changes Auto_track, 1) as ct
INNER JOIN Auto_track pn on pn.ID = CT.ID
WHERE SYS_CHANGE_VERSION > 1 and CT.Sys_Change_Operation <> 'D'



No comments:

Post a Comment