ApexSQL Knowledgebase: How to synchronize data from various off-line sources into a central data repository?

SITE FEED

Site Feed

Support Forum

The fastest and most effective to get answers to your questions quickly.

FAQs

Quick answers to common questions.

Troubleshooting

Known issues and basic troubleshooting techniques for problems or unexpected behaviors.

Knowledgebase

Technical tips, How-to articles, and other tutorials about ApexSQL Tools.

Online Documentation

All of our help files for download or convenient viewing online.

Videos

Watch product demos, training videos, or tutorials of our products' main features.

ApexSQL Knowledgebase

Tips and How-to Articles for Apex SQL Tools

How to synchronize data from various off-line sources into a central data repository?

DESCRIPTION
This article describes a typical data merging scenario, between the 2 data tables, and explains how to customize that scenario.

SOLUTION
A common problem that can be solved using ApexSQL Data Diff data synchronization tool is synchronizing data from various off-line sources into a central data repository. This is a case when mobile devices (laptops) are used to capture data, and later the data is merged to the central data repository.

Here are defined Central and Mobile databases with the Person table.

-- Create script for the Central database
CREATE DATABASE Central
GO
USE Central
GO

CREATE TABLE Person (
ID int PRIMARY KEY IDENTITY(1,1),
personID int NOT NULL,
name NVARCHAR(255) NOT NULL,
address NVARCHAR(255)
)
-- Insert scripts for table: Person
SET IDENTITY_INSERT [dbo].[Person] ON

INSERT INTO [dbo].[Person] ([ID], [personId], [name], [address])
VALUES (1, 1001, N'Central 1', N'Kings Place 200')
INSERT INTO [dbo].[Person] ([ID], [personId], [name], [address])
VALUES (2, 1002, N'Central 2', N'Kings Place 200')
INSERT INTO [dbo].[Person] ([ID], [personId], [name], [address])
VALUES (3, 1003, N'Central 3', N'Kings Place 200')
INSERT INTO [dbo].[Person] ([ID], [personId], [name], [address])
VALUES (4, 1004, N'Central 4', N'Kings Place 200')

SET IDENTITY_INSERT [dbo].[Person] OFF

-- Create script for the Mobile database
CREATE DATABASE Mobile
GO
USE Mobile
GO

CREATE TABLE Person (
ID int PRIMARY KEY IDENTITY(1,1),
personID int NOT NULL,
nameID NVARCHAR(255) NOT NULL,
address NVARCHAR(255)
)
-- Insert scripts for table: Person
SET IDENTITY_INSERT [dbo].[Person] ON

INSERT INTO [dbo].[Person] ([ID], [personId], [name], [address])
VALUES (1, 2001, N'Mobile 1', N'Blumen')
INSERT INTO [dbo].[Person] ([ID], [personId], [name], [address])
VALUES (2, 2002, N'Mobile 2', N'Blumen')
INSERT INTO [dbo].[Person] ([ID], [personId], [name], [address])
VALUES (3, 2003, N'Mobile 3', N'Blumen')

SET IDENTITY_INSERT [dbo].[Person] OFF

Now, try to merge Mobile persons to a Central in the following steps:

Step 1. Select data sources

Set the Mobile database as a source data source and the Central database as a destination data source in ApexSQL Data Diff.

After pressing the Compare button, we will get 3 different rows and a single additional row.

Notice a key sign near the ID field. It means Comparison Key is set to the ID field.
However, this showcase needs to differentiate persons based on personID field, not on ID. The next step will describe how to do that.

Step 2. Set a Comparison Key

From Project Settings Object Filter tab find a Person table, and from the Comparison Key column choose to Edit Custom Comparison Key as in the image (set a personID field as a comparison key and click OK).

Missing persons by personID will be automatically selected for synchronization.

The problem is the additional rows. If you left them selected they will be deleted from the Central database. You should unselect them as in the image.

Step 3. Press the Synchronize button to open the Synchronization Wizard

Make sure that the destination is the Central database.

Make sure Missing Records are selected in the Record Filter step.
After you press Synchronize button you will get the following script:

USE [Central]
GO

BEGIN TRAN
-- =======================================================
-- Synchronization Script for Table: [dbo].[Person]
-- =======================================================
Print 'Synchronization Script for Table: [dbo].[Person]'

SET IDENTITY_INSERT [dbo].[Person] ON

INSERT INTO [dbo].[Person] ([personID], [ID], [name], [address])
VALUES (2001, 1, N'Mobile 1', N'Blumen')
INSERT INTO [dbo].[Person] ([personID], [ID], [name], [address])
VALUES (2002, 2, N'Mobile 2', N'Blumen')
INSERT INTO [dbo].[Person] ([personID], [ID], [name], [address])
VALUES (2003, 3, N'Mobile 3', N'Blumen')

SET IDENTITY_INSERT [dbo].[Person] OFF

COMMIT

Problem with this script is that the primary key ID for the Person table is scripted also causing the error:

Violation of PRIMARY KEY constraint 'PK__Person__3214EC277F60ED59'.
Cannot insert duplicate key in object 'dbo.Person'.

Solution to that problem is not to use the ID field when synchronizing—use the Columns column (Project Settings Object Filter tab), and unselect the ID field as in the image.

Now after we repeat the step 3 the synchronization will went fine.

Summary
ApexSQL Data Diff enables you to:
* Select a Comparison Key (what column or columns ApexSQL Data Diff will track for missing/different/additional records)
* Select Columns you want to synchronize.
* Select row types to synchronize (Different, Missing, Additional)
* Select individual rows to synchronize (using the checkboxes)

Also the other options in ApexSQL Data Diff we haven’t explained here are :

* Disable Foreign Keys and Triggers
* Drop not Unique Indexes
* Disable Check Constraints
* Apply Data Format
* Filtering Rows for Comparison

LAST REVIEW DATE
Nov 09 2009

Labels: Apex SQL Data Diff

posted by Dejan Batanjac on Thursday, June 11, 2009

0 Comments: