Продолжение. Начало - см. пост Репликация таблиц средствами Change Tracking.

 

Пусть имеем накопленные изменения с версии номер 1320.

 

select ct.SYS_CHANGE_VERSION, ct.SYS_CHANGE_CREATION_VERSION, ct.SYS_CHANGE_OPERATION, sct.commit_time, ct.id1, ct.id2, t.fld1, t.fld2  from changetable(changes tbl_1, 1320) ct join sys.dm_tran_commit_table sct on ct.sys_change_version = sct.commit_ts left join tbl_1 t on t.id1 = ct.id1 and t.id2 = ct.id2 order by 1

 

SYS_CHANGE_VERSION

SYS_CHANGE_CREATION_VERSION

SYS_CHANGE_OPERATION

commit_time

id1

id2

fld1

fld2

1322

NULL

D

12/08/2009 13:23:16.963

11

9018645

NULL

NULL

1326

1325

D

12/08/2009 13:23:38.023

14

9979771

NULL

NULL

1328

1324

D

12/08/2009 13:23:47.047

13

9938787

NULL

NULL

1329

1329

I

12/08/2009 13:23:53.060

16

601941

iii

nnn

1332

1327

I

12/08/2009 13:24:06.097

15

354335

kkk

ppp

1333

1331

D

12/08/2009 13:24:09.110

17

878077

NULL

NULL

1335

1321

D

12/08/2009 13:24:18.133

12

9169359

NULL

NULL

1336

1336

I

12/08/2009 13:24:21.143

18

1462335

bbb

yyy

1337

NULL

D

12/08/2009 13:24:23.163

8

7996619

NULL

NULL

1338

NULL

U

12/08/2009 13:24:30.170

10

8995273

yyy

ccc

Скрипт 1

 

Left join в направлении функции changetable нужен потому, что в ней содержатся строки, вызванные операциями удаления, когда в оригинальной таблице соответствующих записей уже нет. По этой же причине поля РК в списке выв��да нужно брать из changetable, а не из оригинальной таблицы.

 

И пусть хотим получить этот результат не в виде рекордсета, а XML вида:

 

<CT_Changes table_name="tbl_1" version_since="1320" version_upto="1338">

  <Record operation="D" change_no="1337" commit_time="2009-08-12T13:24:23.163">

    <PK>

      <id1>8</id1>

      <id2>7996619</id2>

    </PK>

  </Record>

  <Record operation="U" change_no="1338" commit_time="2009-08-12T13:24:30.170">

    <PK>

      <id1>10</id1>

      <id2>8995273</id2>

    </PK>

    <fld1>yyy</fld1>

    <fld2>ccc</fld2>

  </Record>

  <Record operation="I" change_no="1332" commit_time="2009-08-12T13:24:06.097">

    <PK>

      <id1>15</id1>

      <id2>354335</id2>

    </PK>

    <fld1>kkk</fld1>

    <fld2>ppp</fld2>

  </Record>

  <Record operation="I" change_no="1329" commit_time="2009-08-12T13:23:53.060">

    <PK>

      <id1>16</id1>

      <id2>601941</id2>

    </PK>

    <fld1>iii</fld1>

    <fld2>nnn</fld2>

  </Record>

  <Record operation="D" change_no="1333" commit_time="2009-08-12T13:24:09.110">

    <PK>

      <id1>17</id1>

      <id2>878077</id2>

    </PK>

  </Record>

...

</CT_Changes>

Скрипт 2

 

В шапке CT_Changes указывается таблица, изменения по которой смотрим; версия изменений, от которой работала функция changetable; максимальная версия изменений на данный момент. В дочерних элементах Record перечисляются строки результата Скрипт 1. В атрибутах - тип операции (удаление, вставка, обновление), версия этого изменения и время, когда оно случилось. В элементах приводятся значения полей соответствущей записи таблицы-оригинала. Поля, составляющие первичный ключ, выделяются в элемент РК, остальные поля просто перечисляются. В случае удаления остальные поля можно не перечислять, достаточно РК. Для получения такого XML надо написать следующий запрос:

 

declare @n bigint = 1320

select 'tbl_1' as [@table_name], @n as [@version_since], change_tracking_current_version() as [@version_upto],

(

select ct.SYS_CHANGE_OPERATION as [@operation], ct.SYS_CHANGE_VERSION as [@change_no], sct.commit_time as [@commit_time],

ct.id1 as [PK/id1], ct.id2 as [PK/id2], t.fld1 as fld1, t.fld2 as fld2

from changetable(changes tbl_1, 1320) ct

join sys.dm_tran_commit_table sct on ct.sys_change_version = sct.commit_ts

left join tbl_1 t on t.id1 = ct.id1 and t.id2 = ct.id2

for xml path('Record'), type

)

for xml path('CT_Changes')

Скрипт 3

 

Fld1 и fld2 будут NULL в случае SYS_CHANGE_OPERATION = 'D', потому что этой записи уже нет в tbl_1. По умолчанию, в XML они не отразятся, но это и не требуется, т.к. для ее удаления из копии (tbl_2) достаточно иметь значения полей первичного ключа.

Директива type во вложенном select нужна для того, чтобы его результаты воспринимались в виде вложенных элементов XML по отношению к XML, порожденному внешним select'ом. Без нее они будут восприниматься как строки:

 

<CT_Changes table_name="tbl_1" version_since="1320" version_upto="1338">&lt;Record operation="D" change_no="1337" commit_time="2009-08-12T13:24:23.163"&gt;&lt;PK&gt;&lt;id1&gt;8&lt;/id1&gt;&lt;id2&gt;7996619&lt;/id2&gt;&lt;/PK&gt;&lt;/Record&gt;&lt;Record...

 

Решим обратную задачу: XML со Скрипта 2 требуется превратить в табличный вид по типу Скрипт 1.

XQuery, фильтрующий из предыдущего XML операции удаления и возвращающий из них РК удаленных записей:

 

declare @n bigint = 1320

declare @x xml =

(

select 'tbl_1' as [@table_name], @n as [@version_since], change_tracking_current_version() as [@version_upto],

(

select ct.SYS_CHANGE_OPERATION as [@operation], ct.SYS_CHANGE_VERSION as [@change_no], sct.commit_time as [@commit_time],

ct.id1 as [PK/id1], ct.id2 as [PK/id2], t.fld1 as fld1, t.fld2 as fld2

from changetable(changes tbl_1, @n) ct

join sys.dm_tran_commit_table sct on ct.sys_change_version = sct.commit_ts

left join tbl_1 t on t.id1 = ct.id1 and t.id2 = ct.id2

for xml path('Record'), type

)

for xml path('CT_Changes')

)

 

select x.value('(PK/id1)[1]', 'int') id1, x.value('(PK/id2)[1]', 'int') id2 from @x.nodes('CT_Changes/Record[@operation="D"]') d(x)

 

id1   id2

8     7996619

11    9018645

12    9169359

13    9938787

14    9979771

17    878077

Скрипт 4

 

То же для вставки:

 

select x.value('(PK/id1)[1]', 'int') id1, x.value('(PK/id2)[1]', 'int') id2, x.value('fld1[1]', 'nvarchar(10)') fld1, x.value('fld2[1]', 'nvarchar(10)') fld2 from @x.nodes('CT_Changes/Record[@operation="I"]') d(x)

 

id1          id2                          fld1        fld2

15           354335                  kkk         ppp

16           601941                  iii             nnn

18           1462335                bbb        yyy

Скрипт 5

 

И аналогично для обновления.