create database test go use test go create table t1 ( sid int identity(1,1) not null primary key, sno int not null, sname varchar(200) ) go insert into t1(sno,sname) values(101,'wind') go ALTER DATABASE [test] SET RECOVERY SIMPLE WITH NO_WAIT GO ALTER DATABASE [test] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON) go ALTER TABLE dbo.t1 ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON) go --變更記錄表 create table veridrecord ( lvid bigint not null, nvid bigint not null, isover bigint ) go insert into veridrecord(lvid,nvid) values(1,1) go ---捕獲業務數據表 CREATE TABLE [dbo].[tempt1]( [sid] [int] NOT NULL, [sno] [int] NOT NULL, [sname] [varchar](200) NULL, [addtime] [datetime] NULL ) go ALTER TABLE [dbo].[tempt1] ADD CONSTRAINT [DF_tempt1_addtime] DEFAULT (getdate()) FOR [addtime] GO ----測試數據 insert into t1(sno,sname) values(101,'a') insert into t1(sno,sname) values(102,'b') insert into t1(sno,sname) values(103,'c') insert into t1(sno,sname) values(104,'d') go set nocount on update t1 set sno='9899' where sno=102 go ---查看變更捕獲 DECLARE @synchronization_version bigint DECLARE @this_version bigint DECLARE @pid int =COLUMNPROPERTY( OBJECT_ID('dbo.t1'),'sno','ColumnId') declare @a bigint declare @b bigint declare @c bigint SET @synchronization_version = CHANGE_TRACKING_CURRENT_VERSION() select @a=lvid from veridrecord select @b=nvid from veridrecord select @c=isover from veridrecord if @synchronization_version>@b begin if @c=1 begin update veridrecord set lvid=nvid update veridrecord set nvid=@synchronization_version select @this_version=lvid from veridrecord select @this_version insert into tempt1(sid,sno,sname) select k.sid,k.sno,k.sname from ( SELECT P.sid, P.sno,P.sname, CT.SYS_CHANGE_OPERATION,ct.SYS_CHANGE_VERSION, case when CHANGE_TRACKING_IS_COLUMN_IN_MASK ( @pid , SYS_CHANGE_COLUMNS) =0 then 'NO' when CHANGE_TRACKING_IS_COLUMN_IN_MASK ( @pid , SYS_CHANGE_COLUMNS ) =1 then 'YES' else '其它' end ischange FROM dbo.t1 AS P inner join CHANGETABLE(CHANGES dbo.t1,@this_version) AS CT ON P.sid = CT.sid where ct.SYS_CHANGE_VERSION>@this_version ) k where k.ischange='YES' end end else begin select 'no changes' end update veridrecord set isover=1 go select * from tempt1