SQL 2008 Tracking Change and Discovery Updated Data
I am planning to implement this in an SSIS project. Since I don't have an Enterprise edition of SQL Server 2008, I have to use other methods.
Another way is to use triggers, but I try to avoid many triggers.
With change tracking, I am having difficulty finding the right job. An operation is marked as "I" when it should be "U". What am I doing wrong?
Here's a small sample code. pKey = 2 must be "U"
use master;
go
create database CT_Example;
go
use CT_Example;
go
alter database CT_Example
set change_tracking = on
(change_retention = 2 days, auto_cleanup = on);
go
create table Employee
(
pKey int not null,
Name nvarchar(50) NULL,
CT bigint null,
constraint pk_pKey
primary key (pKey)
);
go
alter table Employee
enable change_tracking
with (track_columns_updated = off);
go
create table Staging
(
pKey int not null,
Name nvarchar(50) NULL,
CT bigint null,
);
go
insert into Employee (pKey,Name)
values
(1,'Jhon'),
(2,'Phill'),
(3,'Jones'),
(4,'Tom');
go
update e set
Name = 'Harry'
from
Employee as e
where
pKey = 2;
go
update a set
a.CT = CHANGE_TRACKING_CURRENT_VERSION()
from
Employee as a;
update e set
Name = 'Gabriel'
from
Employee as e
where
pKey = 2;
insert into Employee (pKey,Name)
values
(5,'Sing'),
(6,'Emily'),
(7,'Jane'),
(8,'Sami');
go
Delete
from
Employee
where
pKey = 3;
declare @last_synchronization_version integer;
SET @last_synchronization_version = (select ct from Staging);
select
*
from
Employee as a
right outer join changetable (changes Employee, @last_synchronization_version) as c
on a.pKey = c.pKey;
go
use master;
go
drop database CT_Example;
go
source to share
Your problem is here:
declare @last_synchronization_version integer;
SET @last_synchronization_version = (select ct from Staging);
And the staging table is empty, too @last_synchronization_version = NULL
. It must also be declared as bigint
.
Try running this:
select
*
from
Employee as a
right outer join changetable (changes Employee, 3) as c
on a.pKey = c.pKey;
go
And you will see a line with pKey = 2 as U.
Or you can ask "What was the last change operation for the pKey = 2 line?" eg:
SELECT @last_synchronization_version = sys_change_version - 1
FROM changetable (VERSION Employee,([pKey]), (2)) y
SELECT sys_change_operation
FROM changetable (changes Employee, @last_synchronization_version) x
WHERE x.pKey=2
You can see that there is no need to store the latest version of the changes every line, it is provided by the SQL Server.
According to the documentation changetable (changes Employee, null)
, the following steps should be performed:
If the value is NULL, all tracked changes are returned.
But maybe not the latest version of the change, but only the first version of all available changes for each line.
You can try this script to see how change tracking works:
use master;
go
create database CT_Example;
go
use CT_Example;
go
alter database CT_Example
set change_tracking = on
(change_retention = 2 days, auto_cleanup = on);
go
create table Employee
(
pKey int not null,
Name nvarchar(50) NULL,
CT bigint null,
constraint pk_pKey
primary key (pKey)
);
go
alter table Employee
enable change_tracking
with (track_columns_updated = off);
go
declare @last_synchronization_version bigint;
insert into Employee (pKey,Name)
values
(1,'Jhon'),
(2,'Phill'),
(3,'Jones'),
(4,'Tom');
SELECT CHANGE_TRACKING_CURRENT_VERSION()
SET @last_synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()-1
SELECT * FROM changetable (changes Employee, @last_synchronization_version) x
update e set
Name = 'Harry'
from
Employee as e
where
pKey = 2;
SELECT CHANGE_TRACKING_CURRENT_VERSION()
SET @last_synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()-1
SELECT * FROM changetable (changes Employee, @last_synchronization_version) x
update e set
Name = 'Gabriel'
from
Employee as e
where
pKey = 2;
SELECT CHANGE_TRACKING_CURRENT_VERSION()
SET @last_synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()-1
SELECT * FROM changetable (changes Employee, @last_synchronization_version) x
insert into Employee (pKey,Name)
values
(5,'Sing'),
(6,'Emily'),
(7,'Jane'),
(8,'Sami');
SELECT CHANGE_TRACKING_CURRENT_VERSION()
SET @last_synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()-1
SELECT * FROM changetable (changes Employee, @last_synchronization_version) x
Delete
from
Employee
where
pKey = 3;
SELECT CHANGE_TRACKING_CURRENT_VERSION()
SET @last_synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()-1
SELECT * FROM changetable (changes Employee, @last_synchronization_version) x
SET @last_synchronization_version = @last_synchronization_version-1
SELECT @last_synchronization_version
SELECT * FROM changetable (changes Employee, @last_synchronization_version) x
SET @last_synchronization_version = @last_synchronization_version-1
SELECT @last_synchronization_version
SELECT * FROM changetable (changes Employee, @last_synchronization_version) x
SET @last_synchronization_version = @last_synchronization_version-1
SELECT @last_synchronization_version
SELECT * FROM changetable (changes Employee, @last_synchronization_version) x
SET @last_synchronization_version = @last_synchronization_version-1
SELECT @last_synchronization_version
SELECT * FROM changetable (changes Employee, @last_synchronization_version) x
use master;
go
drop database CT_Example;
go
source to share