Синхронизация данных таблиц двух баз данных 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 не поддерживается.