Synchronize the data of two SQL Server databases

Carry-over data

Parameters to transfer predefined application data in SQL Server database tables while updating the database schema to a new version

Synchronize predefined data. This feature is primarily designed to transfer predefined data of tables (like catalogs, dictionaries, schemes, options, etc.). To synchronize MERGE statement is used. The application builds a temp table with all rows and merges it with the target table. The rules how to get predefined data from the template database and build a merge script is defined in an XML file. To create a template of this file just save it from the Dialog as a new file and edit manually following the instructions in the comments.

Data synchronization follows at the end, after structure synchronization but before the final script. Predefined data can be exported to the database structure file and stored in a text format (XML), so it is not recommended to carry-over tables of a large size. It might significantly increase the file size or cause the memory overhead after the loading.


Synchronization of predefined data in tables (directories, catalogs, predefined application data)

Updating the version of the database structure is often associated not only with updating its structure, but also with the update of some data predefined by the developer in its tables. To carry over such data to the target database, use the option Carry-over data.

AutosyncDB allows:

  • specify the data of which tables and which fields should be transferred
  • specify the condition to select data from the template database
  • specify the condition of which data should be merged or deleted in the target database
  • disable triggers and turns on the IDENTITY_INSERT option of tables at the time of data synchronization
  • store the carry-over data together with a copy of the template database structure in a file.

Data sync is performed by running a data synchronization script, which generates AutosyncDB. The data synchronization script is added to the resulting script immediately after the structure synchronization script, in other words when the structure of the database is match with the structure of the template.

To synchronize data the MERGE statement is used. Predefined data is added to the temporary table, which serves as the source table. To compare records between them the key fields are used. If the table does not have key fields, then records will be matched by all carried fields whose data type allows to apply comparison operations to them and which are not an IDENTITY field.

The rules of data synchronization are described in an XML file.

Example of XML file 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>

Do not forget that in values of attributes of XML tags, the characters < and > should be represented as &lt; and &gt;.

Example. SQL data synchronization script of predefined dictionary
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, 'Try login', 'The user made an attempt to login in the app', '20170908 22:31:13.323', null, null),
(2, 'Logged in', 'The user successfully logged in', '20170908 22:31:13.323', null, null),
(3, 'Logout', 'The user closed the app', '20170908 22:31:13.323', null, null),
(4, 'Module changed', 'The user changed the current module', '20170908 22:31:13.323', null, null),
(5, 'Dataset opened', 'The user opened the dataset', '20170908 22:31:13.323', null, null),
(6, 'Report called', 'The user called the report', '20170908 22:31:13.323', null, null),
(7, 'Event called', 'The user called the procedure', '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

If there are many lines, then the application will synchronize through the temporary table [dbo].[autosyncdb##TEMP].

You should remember that in MS SQL Server® 2005 the MERGE statement is not supported.