SQL SERVER – Synchronize Data Exclusively with T-SQL

Database developers often face the task to synchronize users’ data. Currently, to solve this task, there was developed quite a big number of various utilities. In this article, we will review the process of creating the new functionality to synchronize data exclusively with T-SQL.

Let’s split the synchronization process with T-SQL onto the sub-tasks:

1. Retrieving the table structure of a master database
2. Preparing data stored in the tables of the master database and saving it at the public locations
3. Turning off relations and triggers between the tables of a target database for synchronization time
4. Synchronization of data between tables
5. Turning on relations and triggers between the tables of the target database

To make the process of creating the synchronization functionality comparatively easy to understand, let’s discuss some restrictions:

1. Synchronization will be elapsing within one server, syntax will be supported on the level of MS SQL Server 2005 or higher
2. The schemes between the synchronized databases are identical
3. Only system data types are supported
4. It’s possible to synchronize any amount of tables at a time
5. The structure of tables is not essential

1) Retrieving the table structure of the master database

The list of tables can be obtained with the following query:

SELECT table_name = '[' + s.name + '].[' + t.name + ']'
FROM sys.tables t
JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]

It’s important to remember that some system views may contain an excessive amount of connections and calls of system functions, that will negatively affect the run-time performance of the query, such as sys.tables:

SELECT *
FROM sys.objects$ o
LEFT JOIN sys.sysidxstats lob ON lob.id = o.[object_id] AND lob.indid <= 1
LEFT JOIN sys.syssingleobjrefs ds ON ds.depid = o.[object_id] AND ds.class = 8 AND ds.depsubid <= 1
LEFT JOIN sys.syssingleobjrefs rfs ON rfs.depid = o.[object_id] AND rfs.class = 42 AND rfs.depsubid = 0
LEFT JOIN sys.syspalvalues ts ON ts.class = 'LEOP' AND ts.value = o.lock_escalation_option
WHERE o.[type] = 'U '

Therefore, you have to use as simple system views as possible. In our case, you can use sys.objects:

SELECT table_name = '[' + s.name + '].[' + t.name + ']'
FROM sys.objects o
JOIN sys.schemas s ON o.[schema_id] = s.[schema_id] WHERE o.[type] = 'U'

To make the query not to return excessive rows, we need to preliminary filter out the tables containing no data:

SELECT table_name = '[' + s.name + '].[' + t.name + ']'
FROM (
SELECT
o.[object_id] , o.[schema_id] , o.name
FROM sys.objects o
WHERE o.[type] = 'U '
AND EXISTS(
SELECT 1
FROM sys.partitions p
WHERE p.[object_id] = o.[object_id] AND p.[rows] > 0
)
) t
JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]

The list of columns containing only system data types can be obtained in the following way:

SELECT
table_name = '[' + s.name + '].[' + o.name + ']'
, column_name = '[' + c.name + ']'
FROM sys.columns c
JOIN sys.objects o ON o.[object_id] = c.[object_id] JOIN sys.schemas s ON o.[schema_id] = s.[schema_id] JOIN sys.types b ON b.user_type_id = c.system_type_id AND b.user_type_id = b.system_type_id
WHERE o.[type] = 'U'

Next, to minimize the amount of data that we pass for comparison, it is suggested not to take into account column values, that have default values and can’t be empty. So, the query will look as follows:

SELECT
table_name = '[' + s.name + '].[' + o.name + ']'
, column_name =
CASE WHEN c2.[object_id] IS NULL
THEN '[' + c.name + ']'
ELSE '[' + c.name + '] = NULLIF([' + c.name + '], ' + c2.[definition] + ')'
END
FROM sys.columns c
JOIN sys.objects o ON o.[object_id] = c.[object_id] JOIN sys.schemas s ON o.[schema_id] = s.[schema_id] JOIN sys.types b ON b.user_type_id = c.system_type_id AND b.user_type_id = b.system_type_id
LEFT JOIN sys.default_constraints c2 ON c.default_object_id = c2.[object_id] AND b.name IN ('int', 'bit', 'char', 'nchar', 'ntext', 'nvarchar', 'text', 'varchar')
AND c.is_nullable = 0
WHERE o.[type] = 'U'
AND c.is_computed = 0

2) Retrieving the data stored in the tables of the master database and saving it in public locations

First of all, we need some interim buffer, into which we will load data from a master database. The tempdb system database is public for any user database on the server, therefore, we can choose it.

Every time synchronization runs, we will check whether the table exists and create it if needed with the following query:

IF OBJECT_ID('tempdb.dbo.MergeTemp', 'U') IS NULL BEGIN
CREATE TABLE tempdb.dbo.MergeTemp
(
[object_name] NVARCHAR(300) NOT NULL
, create_date DATETIME NOT NULL DEFAULT GETDATE()
, data XML NULL
, CONSTRAINT [PK_MergeTemp] PRIMARY KEY CLUSTERED
(
id ASC, [object_name] ASC
) ON [PRIMARY] )
END

Then, it’s necessary to decide how to store data from the master database. The main difficulty within this task is that tables may have different structures. The most obvious option is to create a temporary table for each synchronized table and upload data into it. However, such option may be not optimal because we will have to execute many DDL commands.

Therefore, we suggest to solve this task by means of XML, that can describe almost any database entity. The disadvantage of the suggested method is that not quite a correct XML data structure may be generated for some data types. You can retrieve a list of such data types with the following query:

SELECT t.name
FROM sys.types t
WHERE t.user_type_id != t.system_type_id

Let’s write a query to create XML with dynamic structure:

DECLARE
@Data NVARCHAR(4000)
, @ObjectName NVARCHAR(300)
DECLARE [tables] CURSOR READ_ONLY FAST_FORWARD LOCAL FOR
SELECT
[object_name] , = '
INSERT INTO tempdb.dbo.MergeTemp([object_name], data)
SELECT ''' + quota_object_name + ''', (SELECT ' + object_columns + '
FROM ' + quota_object_name + ' t
FOR XML AUTO, ROOT(''' + [object_name] + '''))'
FROM (
SELECT
[object_name] = s.name + '.' + t.name
, quota_object_name = '[' + s.name + '].[' + t.name + ']'
, object_columns = STUFF((
SELECT ISNULL(', [' + c.name + '] = NULLIF([' + c.name + '], ' + c2.[definition] + ')',
', [' + c.name + ']')
+ CASE WHEN b.name = 'varbinary'
THEN ' = CAST('''' AS XML).value(''xs:base64Binary(sql:column("'+ c.name +'"))'',''VARCHAR(MAX)'')'
ELSE ''
END
FROM sys.columns c WITH (NOLOCK)
JOIN sys.types b WITH (NOLOCK) ON b.user_type_id = c.system_type_id AND b.user_type_id = b.system_type_id
LEFT JOIN sys.default_constraints c2 ON c.default_object_id = c2.[object_id] AND b.name IN ('char', 'nchar', 'ntext', 'nvarchar', 'text', 'varchar')
WHERE t.[object_id] = c.[object_id] AND c.is_computed = 0
ORDER BY c.column_id
FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)')
, 1, 2, '')
FROM (
SELECT
o.[object_id] , o.[schema_id] , o.name
FROM sys.objects o WITH (NOLOCK)
WHERE o.[type] = 'U'
AND EXISTS(
SELECT 1
FROM sys.partitions p WITH (NOLOCK)
WHERE p.[object_id] = o.[object_id] AND p.[rows] > 0
)
AND NOT EXISTS(
SELECT 1
FROM sys.columns c WITH (NOLOCK)
JOIN sys.types t WITH (NOLOCK) ON c.system_type_id = t.system_type_id
WHERE (c.user_type_id != c.system_type_id OR t.name = 'xml')
AND c.[object_id] = o.[object_id] )
) t
JOIN sys.schemas s WITH (NOLOCK) ON t.[schema_id] = s.[schema_id] ) data
OPEN [tables] FETCH NEXT FROM [tables] INTO @ObjectName, @Data
IF (@@FETCH_STATUS = -1)
RAISERROR('Have nothing to export...', 16, 1)
WHILE @@FETCH_STATUS = 0 BEGIN
EXEC sys.sp_executesql @Data
FETCH NEXT FROM [tables] INTO @ObjectName, @Data
END
CLOSE [tables] DEALLOCATE [tables]

Where, the following query will be generated for each table:

INSERT INTO tempdb.dbo.MergeTemp ([object_name], data)
SELECT <TABLE name>,
(
SELECT <columns list>
FROM <TABLE name> t
FOR XML AUTO, ROOT('<table name>')
)

After such query is executed, an XML with table data will be generated and written into the MergeTemp table.

3) Disabling relations and triggers between the tables of the master database for the time of synchronization

At the next stage, we will insert data into tables, therefore it’s preferably to turn off foreign keys and triggers. If you do not, various errors may appear at the inserting stage.

Here is a script to turn off/on foreign keys for tables:

DECLARE
@FKeysOff NVARCHAR(MAX)
, @FKeysOn NVARCHAR(MAX)
;WITH fkeys AS
(
SELECT DISTINCT o.[object_name] FROM tempdb.dbo.MergeTemp mt
JOIN (
SELECT
o.[object_id] , [object_name] = '[' + s.name + '].[' + o.name + ']'
FROM sys.objects o
JOIN sys.schemas s ON o.[schema_id] = s.[schema_id] JOIN sys.foreign_keys fk ON o.[object_id] = fk.parent_object_id
WHERE o.[type] = 'U '
) o ON mt.[object_name] COLLATE DATABASE_DEFAULT = o.[object_name] COLLATE DATABASE_DEFAULT
)
SELECT
@FKeysOff = (
SELECT 'ALTER TABLE ' + fk.[object_name] + ' NOCHECK CONSTRAINT ALL;' + CHAR(13)
FROM fkeys fk
FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)')
, @FKeysOn = (
SELECT 'ALTER TABLE ' + fk.[object_name] + ' CHECK CONSTRAINT ALL;' + CHAR(13)
FROM fkeys fk
FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)')
EXEC sys.sp_executesql @FKeysOff
EXEC sys.sp_executesql @FKeysOn

This query seems cumbrous and it doesn’t take into account more complicated relations between tables. In case when the database contains relatively few tables, the following query (turning off all table triggers and foreign keys in the database) may be used:

EXEC sys.sp_msforeachtable '
ALTER TABLE ? DISABLE TRIGGER ALL
ALTER TABLE ? NOCHECK CONSTRAINT ALL '

4) Data synchronization between tables

We have already access to data from the master database stored in XML.

With the following query we can find the unique identifier of the database object we are going to synchronize:

SELECT
o.[object_id] , mt.[object_name] FROM tempdb.dbo.MergeTemp mt
JOIN (
SELECT
o.[object_id] , [object_name] = '[' + s.name + '].[' + o.name + ']'
FROM sys.objects o
JOIN sys.schemas s ON o.[schema_id] = s.[schema_id] WHERE o.[type] = 'U '
) o ON mt.[object_name] COLLATE DATABASE_DEFAULT = o.[object_name] COLLATE DATABASE_DEFAULT

It’s important to note that the object identifier (object_id) is unique in the database and may be changed when the schema is modified (e.g., adding a new column). That’s why, initially, we were wrote the name of the object instead of its identifier. To synchronize tables, we need to parse data from XML.

As an option, for this, you can use the OPENXML construction:

DECLARE @IDoc INT
EXEC sys.sp_xml_preparedocument @IDoc OUTPUT, @XML
SELECT [<column name>] FROM OPENXML(@IDoc, '
<table name>/t')
WITH ([<column name>] [<column datetype>] EXEC sys.sp_xml_removedocument @IDoc

However, this approach is less effective when parsing significantly large XML structures. Therefore, it was decided to use the construction described below:

Here is an example of a query, that will generate dynamic SQL by XML parsing and synchronization with the target table:

DECLARE
@ObjectID INT
, @ObjName NVARCHAR(300)
, @SQL NVARCHAR(MAX)
DECLARE package CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
SELECT
o.[object_id] , mt.[object_name] FROM tempdb.dbo.MergeTemp mt
JOIN (
SELECT
o.[object_id] , [object_name] = '[' + s.name + '].[' + o.name + ']'
FROM sys.objects o
JOIN sys.schemas s ON o.[schema_id] = s.[schema_id] WHERE o.[type] = 'U '
) o ON mt.[object_name] COLLATE DATABASE_DEFAULT = o.[object_name] COLLATE DATABASE_DEFAULT
OPEN package
FETCH NEXT FROM package INTO @ObjectID, @ObjName
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT @SQL = '
DECLARE @XML XML
SELECT @XML = (
SELECT data
FROM tempdb.dbo.MergeTemp
WHERE [object_name] = ''' + @ObjName + '''
)
IF EXISTS(
SELECT 1
FROM sys.columns c WITH (NOLOCK)
WHERE c.[object_id] = ' + CAST(@ObjectID AS VARCHAR(10)) + '
AND c.is_identity = 1
) SET IDENTITY_INSERT ' + @ObjName + ' ON;
DELETE FROM ' + @ObjName + '
INSERT INTO ' + @ObjName + '(' +
STUFF((
SELECT ', [' + c.name + ']'
FROM sys.columns c
WHERE c.[object_id] = @ObjectID
ORDER BY c.column_id
FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)'), 1, 2, '') + ')
SELECT ' + STUFF((
SELECT
', [' + c.name + '] = ' + CASE WHEN c2.name IS NOT NULL THEN 'ISNULL(' ELSE '' END + 'r.c.value(''@' + c.name + ''', ''' + b.name +
CASE
WHEN b.name IN ('char', 'varchar', 'nchar', 'nvarchar', 'varbinary') AND c.max_length = -1 THEN '(max)'
WHEN b.name IN ('char', 'varchar') AND c.max_length!= -1 THEN '(' + CAST(c.max_length AS VARCHAR(10)) + ')'
WHEN b.name IN ('nchar', 'nvarchar') AND c.max_length!= -1 THEN '(' + CAST(c.max_length / 2 AS VARCHAR(10)) + ')'
WHEN b.name = 'decimal' THEN '(' + CAST(c.[precision] AS NVARCHAR(10)) + ',' + CAST(c.[scale] AS VARCHAR(10)) + ')'
ELSE ''
END + ''')' + CASE WHEN c2.name IS NOT NULL THEN ', ' + c2.[definition] + ')' ELSE '' END
FROM sys.columns c
JOIN sys.types b ON b.user_type_id = c.system_type_id AND b.user_type_id = b.system_type_id AND b.name!= 'xml'
LEFT JOIN sys.default_constraints c2 ON c.default_object_id = c2.[object_id] AND b.name IN ('int', 'bit', 'char', 'nchar', 'ntext', 'nvarchar', 'text', 'varchar')
AND c.is_nullable = 0
WHERE c.[object_id] = @ObjectID
AND c.is_computed = 0
ORDER BY c.column_id
FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)')
, 1, 2, '') + '
FROM @XML.nodes(''' + s.name + '.' + o.name + '/t'') r(c)
'
FROM sys.objects o
JOIN sys.schemas s ON o.[schema_id] = s.[schema_id] WHERE o.[object_id] = @ObjectID
EXEC sys.sp_executesql @SQL
FETCH NEXT FROM package INTO @ObjectID, @ObjName
END
CLOSE package
DEALLOCATE package

The following script will build such query for each synchronized table:

DECLARE @XML XML
SELECT @XML = (
SELECT data
FROM tempdb.dbo.MergeTemp
WHERE [object_name] = '
<table name>'
)
IF EXISTS(
SELECT 1
FROM sys.columns c
WHERE c.[object_id] = <table_id>
AND c.is_identity = 1
) SET IDENTITY_INSERT 
<TABLE name> ON;
DELETE FROM 
<TABLE name>
INSERT INTO 
<TABLE name> (<columns list>)
SELECT [<column name>] = r.c.value('@<column name>', '<column datetype>')
FROM @XML.nodes('
<table name>/t') r(c)

Synchronization can be performed in three steps:

1. Inserting new data
2. Updating existing data by a key field
3. Deleting non-existing data in the master database

The provided sequence of actions can be organized through separate operators: INSERT/UPDATE/DELETE; or using a MERGE construction that was implemented in MS SQL Server 2008. However, in this case, SQL code will have quite a complicated structure, since we will have to take into account many factors (e.g., insert data in accordance to the order of the relations between tables). In case if the database has a more simple structure, an easier approach can be used.

If all the tables of the database or a specific isolated schema are synchronized, there is a possibility to directly remove all the records from the tables and insert data from the master database. On one hand, such approach can be the most cost-effective one in terms of implementation, if a database with simple logic is synchronized. On the other hand, this way of synchronization may lead to damages in the integrity and validity of the data in the database.

5) Enabling relations and triggers between the tables of the target database

After inserting data into required tables, it’s necessary to enable foreign keys and triggers in order not to damage the cascade integrity and internal business logic. This can be performed as follows:

EXEC sys.sp_msforeachtable '
ALTER TABLE ? ENABLE TRIGGER ALL
ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'

When executing this command, the server will check whether the inserted data is correct. Therefore, if an error arises on this step, the problem may initially due to damaging of the cascade integrity.

The resulting version of the script:

USE AdventureWorks2008R2
SET NOCOUNT ON;
SET XACT_ABORT ON;
RAISERROR('Start...', 0, 1) WITH NOWAIT
DECLARE
@SQL NVARCHAR(MAX)
, @Time DATETIME
, @Text NVARCHAR(300)
, @SessionUID UNIQUEIDENTIFIER
SELECT
@SQL = ''
, @Time = GETDATE()
, @SessionUID = NEWID()
SELECT @Text = CONVERT(NVARCHAR(25), @Time, 114) + ' Creating temporary table...'
RAISERROR(@Text, 0, 1) WITH NOWAIT
IF OBJECT_ID('tempdb.dbo.MergeTemp', 'U') IS NULL BEGIN
CREATE TABLE tempdb.dbo.MergeTemp
(
id UNIQUEIDENTIFIER NOT NULL
, [object_name] NVARCHAR(300) NOT NULL
, create_date DATETIME NOT NULL DEFAULT GETDATE()
, data XML NULL
, CONSTRAINT [PK_MergeTemp] PRIMARY KEY CLUSTERED
(
id ASC, [object_name] ASC
) ON [PRIMARY] )
END
SELECT @Text = CONVERT(NVARCHAR(25), GETDATE(), 114) + ' Generate SQL queries...'
RAISERROR(@Text, 0, 1) WITH NOWAIT
DECLARE
@Data NVARCHAR(4000)
, @ObjectName NVARCHAR(300)
DECLARE [tables] CURSOR READ_ONLY FAST_FORWARD LOCAL FOR
SELECT
[object_name] , = '
INSERT INTO tempdb.dbo.MergeTemp(id, [object_name], data)
SELECT ''' + CAST(@SessionUID AS VARCHAR(36)) + ''', ''' + quota_object_name + ''', (SELECT ' + object_columns + '
FROM ' + quota_object_name + ' t
FOR XML AUTO, ROOT(''' + [object_name] + '''))'
FROM (
SELECT
[object_name] = s.name + '.' + t.name
, quota_object_name = '[' + s.name + '].[' + t.name + ']'
, object_columns = STUFF((
SELECT ISNULL(', [' + c.name + '] = NULLIF([' + c.name + '], ' + c2.[definition] + ')',
', [' + c.name + ']')
+ CASE WHEN b.name = 'varbinary'
THEN ' = CAST('''' AS XML).value(''xs:base64Binary(sql:column("'+ c.name +'"))'',''VARCHAR(MAX)'')'
ELSE ''
END
FROM sys.columns c WITH (NOLOCK)
JOIN sys.types b WITH (NOLOCK) ON b.user_type_id = c.system_type_id AND b.user_type_id = b.system_type_id
LEFT JOIN sys.default_constraints c2 ON c.default_object_id = c2.[object_id] AND b.name IN ('char', 'nchar', 'ntext', 'nvarchar', 'text', 'varchar')
WHERE t.[object_id] = c.[object_id] AND c.is_computed = 0
ORDER BY c.column_id
FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)')
, 1, 2, '')
FROM (
SELECT
o.[object_id] , o.[schema_id] , o.name
FROM sys.objects o WITH (NOLOCK)
WHERE o.[type] = 'U'
AND EXISTS(
SELECT 1
FROM sys.partitions p WITH (NOLOCK)
WHERE p.[object_id] = o.[object_id] AND p.[rows] > 0
)
AND NOT EXISTS(
SELECT 1
FROM sys.columns c WITH (NOLOCK)
JOIN sys.types t WITH (NOLOCK) ON c.system_type_id = t.system_type_id
WHERE (c.user_type_id!= c.system_type_id OR t.name = 'xml')
AND c.[object_id] = o.[object_id] )
) t
JOIN sys.schemas s WITH (NOLOCK) ON t.[schema_id] = s.[schema_id] -- You can specify sync all db tables or specified schema/tables
-- WHERE s.name = 'Sales'
-- OR o.name IN ('Address', 'AddressType')
) data
OPEN [tables] FETCH NEXT FROM [tables] INTO @ObjectName, @Data
IF (@@FETCH_STATUS = -1)
RAISERROR('Have nothing to export...', 16, 1)
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT @Text = CONVERT(NVARCHAR(25), GETDATE(), 114) + ' Generate XML for ' + @ObjectName
RAISERROR(@Text, 0, 1) WITH NOWAIT
EXEC sys.sp_executesql @Data
FETCH NEXT FROM [tables] INTO @ObjectName, @Data
END
CLOSE [tables] DEALLOCATE [tables] PRINT 'ELAPSED TIME: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS NVARCHAR(15)) + 'ms'
RAISERROR('Finish...', 0, 1) WITH NOWAIT
PRINT REPLICATE('-', 80)
USE AdventureWorks2008R2_Live
RAISERROR('Start...', 0, 1) WITH NOWAIT
SELECT @Time = GETDATE()
SELECT @Text = CONVERT(NVARCHAR(25), @Time, 114) + ' Get similar objects in both databases...'
RAISERROR(@Text, 0, 1) WITH NOWAIT
DECLARE @MergeTemp TABLE
(
[object_name] NVARCHAR(300)
, [object_id] INT
, data XML
)
INSERT INTO @MergeTemp
(
[object_name] , [object_id] , daata
)
SELECT
mt.[object_name] , o.[object_id] , mt.data
FROM tempdb.dbo.MergeTemp mt
JOIN (
SELECT
o.[object_id] , [object_name] = '[' + s.name + '].[' + o.name + ']'
FROM sys.objects o WITH (NOLOCK)
JOIN sys.schemas s WITH (NOLOCK) ON o.[schema_id] = s.[schema_id] WHERE o.[type] = 'U'
) o ON mt.[object_name] COLLATE DATABASE_DEFAULT = o.[object_name] COLLATE DATABASE_DEFAULT
WHERE mt.id = @SessionUID
IF NOT EXISTS(
SELECT 1
FROM @MergeTemp
) RAISERROR('Have nothing to import...', 16, 1)
SELECT @Text = CONVERT(NVARCHAR(25), GETDATE(), 114) + ' Turn off foreign keys and triggers...'
RAISERROR(@Text, 0, 1) WITH NOWAIT
EXEC sys.sp_msforeachtable '
ALTER TABLE ? DISABLE TRIGGER ALL
ALTER TABLE ? NOCHECK CONSTRAINT ALL
'
DECLARE
@ObjectID INT
, @ObjName NVARCHAR(300)
DECLARE package CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
SELECT
mt.[object_id] , mt.[object_name] FROM @MergeTemp mt
OPEN package
FETCH NEXT FROM package INTO
@ObjectID
, @ObjName
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT @Text = CONVERT(NVARCHAR(25), GETDATE(), 114) + ' Processing ' + @ObjName + '...'
RAISERROR(@Text, 0, 1) WITH NOWAIT
SELECT @SQL = '
DECLARE @XML XML
SELECT @XML = (
SELECT data
FROM tempdb.dbo.MergeTemp
WHERE [object_name] = ''' + @ObjName + ''' AND id = ''' + CAST(@SessionUID AS VARCHAR(36)) + '''
)
IF EXISTS(
SELECT 1
FROM sys.columns c WITH (NOLOCK)
WHERE c.[object_id] = ' + CAST(@ObjectID AS VARCHAR(10)) + '
AND c.is_identity = 1
) SET IDENTITY_INSERT ' + @ObjName + ' ON;
DELETE FROM ' + @ObjName + '
INSERT INTO ' + @ObjName + '(' +
STUFF((
SELECT ', [' + c.name + ']'
FROM sys.columns c WITH (NOLOCK)
WHERE c.[object_id] = @ObjectID
AND c.is_computed = 0
ORDER BY c.column_id
FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)'), 1, 2, '') + ')
SELECT ' + STUFF((
SELECT
', [' + c.name + '] = ' + CASE WHEN c2.name IS NOT NULL THEN 'ISNULL(' ELSE '' END + 'r.c.value(''@' + c.name + ''', ''' + b.name +
CASE
WHEN b.name IN ('char', 'varchar', 'nchar', 'nvarchar', 'varbinary') AND c.max_length = -1 THEN '(max)'
WHEN b.name IN ('char', 'varchar') AND c.max_length!= -1 THEN '(' + CAST(c.max_length AS VARCHAR(10)) + ')'
WHEN b.name IN ('nchar', 'nvarchar') AND c.max_length!= -1 THEN '(' + CAST(c.max_length / 2 AS VARCHAR(10)) + ')'
WHEN b.name = 'decimal' THEN '(' + CAST(c.[precision] AS NVARCHAR(10)) + ',' + CAST(c.[scale] AS VARCHAR(10)) + ')'
ELSE ''
END + ''')' + CASE WHEN c2.name IS NOT NULL THEN ', ' + c2.[definition] + ')' ELSE '' END
FROM sys.columns c WITH (NOLOCK)
JOIN sys.types b WITH (NOLOCK) ON b.user_type_id = c.system_type_id AND b.user_type_id = b.system_type_id AND b.name!= 'xml'
LEFT JOIN sys.default_constraints c2 WITH (NOLOCK) ON c.default_object_id = c2.[object_id] AND b.name IN ('bit', 'char', 'varchar', 'nchar', 'nvarchar')
WHERE c.[object_id] = @ObjectID
AND c.is_computed = 0
ORDER BY c.column_id
FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)')
, 1, 2, '') + '
FROM @XML.nodes(''' + s.name + '.' + o.name + '/t'') r(c)
'
FROM sys.objects o WITH (NOLOCK)
JOIN sys.schemas s WITH (NOLOCK) ON o.[schema_id] = s.[schema_id] WHERE o.[object_id] = @ObjectID
EXEC sys.sp_executesql @SQL
FETCH NEXT FROM package INTO
@ObjectID
, @ObjName
END
CLOSE package
DEALLOCATE package
SELECT @Text = CONVERT(NVARCHAR(25), GETDATE(), 114) + ' Turn on foreign keys and triggers...'
RAISERROR(@Text, 0, 1) WITH NOWAIT
EXEC sys.sp_msforeachtable '
ALTER TABLE ? ENABLE TRIGGER ALL
ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL
'
PRINT 'ELAPSED TIME: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS NVARCHAR(15)) + 'ms'
RAISERROR('Finish...', 0, 1) WITH NOWAIT

Conclusion:

As we can see, even for databases with a simple structure, the synchronization process is quite a bigtask that includes many different actions.

As you have noticed early in the article, manual data synchronization is not quite easy task. Thus, to save time and efforts it is highly recommended to use special tools for synchronization. For instance, the following screen-shot demonstrates how you can visualize compared data before synchronization by using comparison feature of dbForge Studio for SQL Server.

The upper grid shows the database objects differences and the bottom grid displays data differences. To synchronize source and target databases you can automatically generate the synchronization script. Then you can view this script, save it, or execute it immediately to update the target database. So the process can be handled in an easier way.

Reference: Pinal Dave (https://blog.sqlauthority.com)