Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Фиксация изменений в строках таблиц (+фиксация имён изменённых колонок)  [new]
Leax
Member

Откуда: Киев
Сообщений: 438
Приветствую,

Появилась довольно сложная задача: нужно формировать xml с изменениями, происшедшими в таблицах. Причём есть состояния таблиц ДО изменений и ПОСЛЕ изменений. Кроме того, есть условие, что в xml нужно фиксировать изменения только в тех столбцах, где они действительно были.

Ну вот как-то так:

-- Есть две таблицы: таблица @t2- это таблица @t1, но после изменений в отдельных столбцах
-- Есть данные только по ключевым полям, которых может быть несколько. В данном примере ключевым столбцом является id. 
-- Число, тип и названия остальных столбцов заранее неизвестны- их может быть очень много
declare @t1 table (id int primary key, a varchar(max), b int, c date)
insert @t1 values 
(1, 'тест1', 5, '20131230'), 
(2, 'тест2', 6, '20131231')


declare @t2 table (id int primary key, a varchar(max), b int, c date)
insert @t2 values 
(1, 'тест1d', 15, '20131230'), 
(2, 'тест2', 6, null)

select * from @t1
select * from @t2


Программа-минимум. Хочется получить таблицу изменений вида:
declare @tt table (id int, columnName varchar(max), value2 varchar(max))
1 'a' 'тест1d'
1 'b' '15'
2 'd' null


Программа-максимум. Хочется получить xml вида:
<root>
  <row id="1" a="тест1d" b="15">
  <row id="2" d="null">
</root>


Пока что в голове появляются только варианты с Dynamic SQL, где для каждой строке в таблицах поочередно берутся данные из каждого столбца (имена всех столбцов таблицы берем из системных таблиц), преобразовываются в varchar, а затем сравниваются, после чего отличающиеся данные постепенно добавляются в переменную типа varchar(max), которая затем добавляется в постепенно накапливаемую xml.

Однако, нутром чувствую, что это крайне корявое решение. Еще возникают мысли насчёт PIVOT/UNPIVOT, но это тоже крайне туманный вариант.
Кроме того, возникает проблема с тем, что нужен скрипт, в котором желательно явно не указывать имена столбцов (кроме ключевых), чтобы можно было с наименьшими усилиями использовать его для сравнения различных таблиц с большим числом колонок.

Хотелось бы узнать ваши мысли по этому поводу...
11 дек 13, 14:35    [15277088]     Ответить | Цитировать Сообщить модератору
 Re: Фиксация изменений в строках таблиц (+фиксация имён изменённых колонок)  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
ЧенджТреккинг
11 дек 13, 14:39    [15277117]     Ответить | Цитировать Сообщить модератору
 Re: Фиксация изменений в строках таблиц (+фиксация имён изменённых колонок)  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
Как-то так:
select
 case when not exists(select t1.a intersect select t2.a) then isnull(cast(t2.a as varchar(100)), 'null') end as [@a],
 case when not exists(select t1.b intersect select t2.b) then isnull(cast(t2.b as varchar(100)), 'null') end as [@b],
 case when not exists(select t1.c intersect select t2.c) then isnull(cast(t2.c as varchar(100)), 'null') end as [@c]
from
 @t1 t1 join
 @t2 t2 on t2.id = t1.id
where
 not exists(select t1.a, t1.b, t1.c intersect select t2.a, t2.b, t2.c)
for xml path('row'), root('Root');
11 дек 13, 14:59    [15277265]     Ответить | Цитировать Сообщить модератору
 Re: Фиксация изменений в строках таблиц (+фиксация имён изменённых колонок)  [new]
Leax
Member

Откуда: Киев
Сообщений: 438
Knyazev Alexey
ЧенджТреккинг

в ChangeTracking есть небольшая проблемка с апдейтами вида:
update tbl set field1=field1, т.к. запись уже считается изменённой (и подлежит синхронизации). У нас подобными вещами любят злоупотреблять...
В CDC такой проблемы нет, потому им и решили пользоваться.

invm
Как-то так:

Вариант хороший, но проблема с явным перечислением 100500 имён столбцов осталась
11 дек 13, 15:12    [15277395]     Ответить | Цитировать Сообщить модератору
 Re: Фиксация изменений в строках таблиц (+фиксация имён изменённых колонок)  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
Leax
но проблема с явным перечислением 100500 имён столбцов осталась
Можно написать "перечислятор", генерирующий текст запроса.
11 дек 13, 15:23    [15277487]     Ответить | Цитировать Сообщить модератору
 Re: Фиксация изменений в строках таблиц (+фиксация имён изменённых колонок)  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
invm
isnull(cast(t2.a as varchar(100)), 'null')
Иногда в значениях также используют ссылочный тип, ну то есть так и написать вместо null: xsi:nil
isnull(cast(t2.a as varchar(100)), 'xsi:nil')
Хотя по спецификации это относится только к нодам, если я не путаю.
А вообще гипотетически путаница может быть, если в строках явно пишется null.
12 дек 13, 01:42    [15280334]     Ответить | Цитировать Сообщить модератору
 Re: Фиксация изменений в строках таблиц (+фиксация имён изменённых колонок)  [new]
Leax
Member

Откуда: Киев
Сообщений: 438
invm
Leax
но проблема с явным перечислением 100500 имён столбцов осталась
Можно написать "перечислятор", генерирующий текст запроса.

Так и сделал, благодарю за хороший пример и дельный совет
12 дек 13, 09:11    [15280586]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить