Datafly.pro

-- Création d'une base et d'une table source
USE master
GO
CREATE DATABASE ChangeTrackingSource
go
USE ChangeTrackingSource
SELECT  [BusinessEntityID]
      , [PersonType]
      , [FirstName]
      , [LastName]
INTO    person
FROM    AdventureWorks2008R2.Person.Person 
go
ALTER TABLE person
ADD CONSTRAINT pk_person PRIMARY KEY CLUSTERED ( BusinessEntityID )
  -- Une PK est nécessaire pour le change tracking
go
-- Création d'une base et table destination
USE master
go
CREATE DATABASE ChangeTrackingTarget
go
USE ChangeTrackingTarget
Go
CREATE TABLE [SynchroPerson]
    (
      [BusinessEntityID] [int] NOT NULL
    , [PersonType] [nchar](2) NULL
    , [FirstName] NVARCHAR(50) NULL
    , [LastName] NVARCHAR(50) NULL
    , CONSTRAINT [PK_Person_BusinessEntityID] PRIMARY KEY CLUSTERED ( [BusinessEntityID] ASC )
    )
GO
INSERT  SynchroPerson
        SELECT  *
        FROM    ChangeTrackingSource..person
 -- mêmes données
GO
-- Je crée aussi une table pour conserver les versions de la source pour le change tracking
CREATE TABLE CT_SourceVersion ( CT_version BIGINT )
INSERT  CT_SourceVersion
        DEFAULT VALUES
GO

-- Activation du change tracking pour la base et pour la table source
USE master
GO
ALTER DATABASE ChangeTrackingSource SET CHANGE_TRACKING= ON
( CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON
)
GO
USE ChangeTrackingSource
GO
ALTER TABLE person ENABLE CHANGE_TRACKING
    WITH (
         TRACK_COLUMNS_UPDATED = ON)
GO


-- initialisation du change tracking, mes tables sont synchrones et je stocke la version actuelle
UPDATE  ChangeTrackingTarget..CT_SourceVersion
SET     CT_Version = ( SELECT   CHANGE_TRACKING_CURRENT_VERSION()
                     )



-- faire quelques mises à jour, ajouts et suppressions successifs dans ma source
UPDATE  Person
SET     FirstName = 'paul'
      , LastName = 'allen'
WHERE   BusinessEntityID = 1
go
UPDATE  Person
SET     FirstName = 'bill'
      , LastName = 'gates'
WHERE   BusinessEntityID = 1
go
INSERT  Person
        (
          BusinessEntityID
        , PersonType
        , FirstName
        , LastName
        )
VALUES  (
          310
        , 'SC'
        , 'Joan'
        , 'Ballmer'
        )
go
UPDATE  Person
SET     FirstName = 'steve'
WHERE   BusinessEntityID = 310
GO
DELETE  person
WHERE   BusinessEntityID = 2
GO

----------- Le change tracking, je vais synchroniser mes tables ----
SELECT  *
FROM    ChangeTrackingTarget..SynchroPerson
WHERE   BusinessEntityID IN ( 1, 2, 310 )
   -- juste pour montrer que les modification ne sont pas dans ma cible

DECLARE @sync_version BIGINT
SELECT  @sync_version = CT_version
FROM    ChangeTrackingTarget..CT_SourceVersion
   -- je récupère la version
-- et j'affiche les modifications de ma source depuis cette version
SELECT  CT.BusinessEntityID
      , p.persontype
      , p.firstname
      , p.lastname
      , CT.SYS_CHANGE_OPERATION
      , CT.SYS_CHANGE_COLUMNS
      , CT.SYS_CHANGE_CONTEXT
FROM    CHANGETABLE(CHANGES Person, @sync_version) CT
        LEFT JOIN Person p ON CT.BusinessEntityID = p.BusinessEntityID


-- Je peux donc synchroniser : INSERT, UPDATE, DELETE
DECLARE @sync_version BIGINT
SELECT  @sync_version = CT_version
FROM    ChangeTrackingTarget..CT_SourceVersion

INSERT  ChangeTrackingTarget..SynchroPerson
        (
          BusinessEntityID
        , PersonType
        , FirstName
        , LastName
        )
        SELECT  CT.BusinessEntityID
              , p.persontype
              , p.firstname
              , p.lastname
        FROM    CHANGETABLE(CHANGES Person, @sync_version) CT
                JOIN Person p ON CT.BusinessEntityID = p.BusinessEntityID
                                 AND CT.SYS_CHANGE_OPERATION = 'I'

UPDATE  ChangeTrackingTarget..SynchroPerson
SET     FirstName = p.Firstname
      , LastName = p.Lastname
      , PersonType = p.Persontype
FROM    ChangeTrackingTarget..SynchroPerson
        JOIN CHANGETABLE(CHANGES Person, @sync_version) CT ON ChangeTrackingTarget..SynchroPerson.BusinessEntityID = CT.BusinessEntityID
                                                              AND CT.SYS_CHANGE_OPERATION = 'U'
        JOIN Person p ON CT.BusinessEntityID = p.BusinessEntityID

DELETE  ChangeTrackingTarget..SynchroPerson
WHERE   BusinessEntityID in (
        SELECT  CT.BusinessEntityID
        FROM    CHANGETABLE(CHANGES Person, @sync_version) CT
        WHERE   CT.SYS_CHANGE_OPERATION = 'D' )

-- Vérifions
SELECT  *
FROM    ChangeTrackingTarget..SynchroPerson
WHERE   BusinessEntityID IN ( 1, 2, 310 )

-- si tout s'est bien passé, je conserve le nouveau numéro de version pour la prochaine fois
UPDATE  ChangeTrackingTarget..CT_SourceVersion
SET     CT_Version = ( SELECT   CHANGE_TRACKING_CURRENT_VERSION()
                     )