Синхронізація даних таблиць двох баз даних SQL Server

Данні для перенесення

Параметри перенесення заздалегідь визначених даних програм в таблицях баз даних SQL Server під час оновлення структури БД до нової версії

Синхронізувати заздалегідь визначені дані. Ця функція в першу чергу призначена для передачі заздалегідь визначених даних таблиць (наприклад, каталоги, словники, схеми, опції і т.д.). Для синхронізації даних використовується оператор MERGE. Програма створює тимчасову таблицю з усіма рядками і виконує її злиття з цільової таблицею. Правила, як отримати заздалегідь визначені дані з шаблонної бази даних і побудувати скрипт злиття, визначаються в XML-файлі. Щоб створити шаблон цього файлу – просто збережіть його з-під Діалогу як новий файл і відредагуйте вручну, відповідно до інструкцій в коментарях.

Синхронізація даних відбувається в кінці, після синхронізації структури, але перед фінальним скриптом. Заздалегідь визначені дані можуть бути експортовані в файл структури бази даних і зберігаються в текстовому форматі (XML), тому не рекомендується переносити таблиці великого розміру. Це може значно збільшити розмір файлу або привести до переповнення пам'яті після завантаження.


Синхронізація даних заздалегідь визначених таблиць (опціональні словники, дані програми)

Оновлення версії бази даних часто пов'язано не тільки з оновленням її структури, а й з оновленням деяких заздалегідь визначених розробником даних в її таблицях. Для перенесення таких даних й використовується опція Данні для перенесення.

AutosyncDB дозволяє:

  • вказувати дані яких таблиць і в складі яких стовпчиків повинні бути перенесені
  • обмежувати умовою вибірку даних для перенесення з шаблонної бази даних
  • обмежувати умовою вибірку даних для зміни і видалення в цільовій базі даних
  • відключати тригера і IDENTITY таблиць в момент синхронізації даних
  • зберігати дані для перенесення разом з копією шаблонної структури БД у файлі

Перенесення даних здійснюється шляхом виконання скрипта синхронізації даних, який створює AutosyncDB. Скрипт синхронізації даних додається в результуючий скрипт відразу ж після скрипта синхронізації структури, тобто коли структура бази даних вже повністю буде відповідати структурі шаблону.

Для синхронізації даних використовується оператор MERGE. Дані для перенесення додаються в тимчасову таблицю, яка служить таблицею-джерелом. Для зіставлення записів між собою використовуються ключові поля (PRIMARY KEY). Якщо таблиця не має ключових полів, то записи будуть зіставлятися по усіх полях, що переносяться, тип даних яких дозволяє застосовувати до них операції порівняння і які не є IDENTITY.

Правила перенесення даних описуються в XML-файлі. Його структура приведена в шаблоні, який завжди можна отримати, якщо при виборі файлу вказати ім'я нового XML-файлу.

Приклад файлу CarryOverData.xml
<?xml version="1.0"?>
<ROOT>
<!--
Tag
	<Table>

Attributes
	{Name}          - Table name
	{Owner}         - Schema
	[IsTriggerOff]  - Disable the table trigger(s)
	[IsIdentityOff] - turn on IDENTITY_INSERT
	[IsDelete]      - Delete rows that NOT MATCHED BY SOURCE in the MERGE statement
	[MoveFields]    - List of columns to move (if the attribute doesn't exist = all columns)
	[MoveForUpdate] - List of columns to update (if the attribute doesn't exist = MoveFields or all columns)
	[SelectWhere]   - WHERE clause to select data from a template database
	[UpdateWhere]   - WHERE clause to update target data in the MERGE statement with '_S' as source and '_T' as target tables aliases: WHEN MATCHED [(AND UpdateWhere)] THEN
	[DeleteWhere]   - WHERE clause to delete target data in the MERGE statement with '_T' as target table alias: WHEN NOT MATCHED BY SOURCE [(AND DeleteWhere)] THEN

	EXAMPLE:
-->

	<Table Name="UserEventTypes" Owner="dbo" IsTriggerOff="false" IsIdentityOff="false" IsDelete="true"></Table>
	<Table Name="TicketStates" Owner="dbo" IsTriggerOff="true" IsIdentityOff="false" IsDelete="true" />
	<Table Name="TicketSources" Owner="dbo" IsTriggerOff="true" IsIdentityOff="false" IsDelete="true" Fields="TicketSourcesID, SourceName" />
	<Table Name="AppCodes" Owner="dbo" IsTriggerOff="true" IsIdentityOff="false" IsDelete="true" MoveForUpdate="Name,CodeString,Dsc" />
	<Table Name="Channels" Owner="dbo" IsTriggerOff="true" IsIdentityOff="true" IsDelete="true"
		SelectWhere="[ChannelsID] < 100"
		UpdateWhere="1=0"
		DeleteWhere="_T.[ChannelsID] < 100"
		MoveFields="ChannelsID,Name,Dsc" />


</ROOT>

Слід пам'ятати, що в значеннях атрибутів XML-тегів символи < і > повинні бути представлені як &lt; і &gt;.

Приклад SQL-скрипта синхронізації даних опціонального словника
DECLARE @UserEventTypes TABLE
(
	[IdUserEventTypes] int NOT NULL,
	[Name] varchar(500) NULL,
	[Dsc] varchar(1000) NULL,
	[DateIns] datetime NULL,
	[DateUpd] datetime NULL,
	[DateDel] datetime NULL
);

INSERT INTO @UserEventTypes([IdUserEventTypes], [Name], [Dsc], [DateIns], [DateUpd], [DateDel]) VALUES
(1, 'Попытка регистрации в программе', 'Пользователь предпринял попытку войти в программу', '20170908 22:31:13.323', null, null),
(2, 'Регистрация в программе', 'Пользователь вошел в программу', '20170908 22:31:13.323', null, null),
(3, 'Выход из программы', 'Пользователь закрыл програму', '20170908 22:31:13.323', null, null),
(4, 'Выбор модуля', 'Пользователь сменил текущий модуль', '20170908 22:31:13.323', null, null),
(5, 'Открытие режима', 'Пользователь вызвал режим', '20170908 22:31:13.323', null, null),
(6, 'Вызов отчета', 'Пользователь вызвал отчет', '20170908 22:31:13.323', null, null),
(7, 'Вызов действия', 'Пользователь вызвал действие', '20170908 22:31:13.323', null, null);

MERGE [dbo].[UserEventTypes] AS _T
USING @UserEventTypes AS _S
	ON (_T.[IdUserEventTypes] = _S.[IdUserEventTypes])
WHEN MATCHED THEN
	UPDATE SET
		_T.[Name] = _S.[Name], _T.[Dsc] = _S.[Dsc], _T.[DateIns] = _S.[DateIns], _T.[DateUpd] = _S.[DateUpd], _T.[DateDel] = _S.[DateDel]
WHEN NOT MATCHED BY TARGET THEN
	INSERT([IdUserEventTypes], [Name], [Dsc], [DateIns], [DateUpd], [DateDel])
	VALUES(_S.[IdUserEventTypes], _S.[Name], _S.[Dsc], _S.[DateIns], _S.[DateUpd], _S.[DateDel])
WHEN NOT MATCHED BY SOURCE THEN
	DELETE;
GO

Якщо рядків багато, то програма буде виконувати синхронізацію через тимчасово створювану таблицю [dbo].[autosyncdb##TEMP].

Слід пам'ятати, що в MS SQL Server® 2005 оператор MERGE не підтримується.