How To Synchronise Data Across Two SQL Server Databases – Part 1. SQL Code And Application For Individual Objects Processing

Note: This series is comprised of two posts – this one and another one which can be viewed HERE.

Lately I have been working on a project which involves synchronising data across two different databases without using any proprietary solution. As you know, there are many great tool out there which can automate such process hassle-free, however, given the requirements set out by the client i.e. having to use existing SQL Server infrastructure with no third-party tools, the project posed some interesting challenges. To cut the long story short, solution architect in consultation with the client decided to settle on utilising log shipping but I also experimented with other approaches, one of those described below. Given the fact that the data required to be synchronised was heavily de-normalised and that all of the tables had a primary key constraint on them I took advantage of the SQL code encapsulated in a stored procedure, which was preliminarily developed by my colleague – Fredy. The code builds MERGE SQL statement on the fly based on database objects’ metadata and as long the table has a primary key constraint present, it automatically handles INSERT and UPDATE based on its content.

Let’s take a closer look at the possible scenario where such dynamic MERGE statement could be used. Suppose we have two tables, one created on a source database and one on target database. The source table has just been updated with some new data and also some of the old data has been modified. Target table, on the other hand, has some overlapping data, however, given that the source has is more up-to-date, target object requires to be synchronised to replicate the changes – both inserts and updates. To materialise this scenario on a database level, let’s execute the following SQL and create the above databases, objects and some dummy data.

USE [master]
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Source_DB')
BEGIN
-- Close connections to the DW_Sample database
ALTER DATABASE [Source_DB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [Source_DB]
END
GO
CREATE DATABASE [Source_DB] 

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Target_DB')
BEGIN
-- Close connections to the DW_Sample database
ALTER DATABASE [Target_DB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [Target_DB]
END
GO
CREATE DATABASE [Target_DB]

USE Source_DB
CREATE TABLE Source_Tbl (
ID int NOT NULL,
Sample_Data_Col1 varchar (50) NOT NULL,
Sample_Data_Col2 varchar (50) NOT NULL,
Sample_Data_Col3 varchar (50) NOT NULL)
GO

USE Target_DB
CREATE TABLE Target_Tbl (
ID int NOT NULL,
Sample_Data_Col1 varchar (50) NOT NULL,
Sample_Data_Col2 varchar (50) NOT NULL,
Sample_Data_Col3 varchar (50) NOT NULL)
GO

USE Source_DB
DECLARE @rowcount int = 0
WHILE @rowcount < 1000
	BEGIN
		SET NOCOUNT ON
		INSERT INTO Source_Tbl
		(ID, Sample_Data_Col1, Sample_Data_Col2, Sample_Data_Col3)
		SELECT 
		@rowcount, 
		'Sample_Data' + CAST(@rowcount as varchar(10)), 
		'Sample_Data' + CAST(@rowcount as varchar(10)), 
		'Sample_Data' + CAST(@rowcount as varchar(10))
		SET @rowcount = @rowcount + 1
	END
GO

USE Target_DB
DECLARE @rowcount int = 0
WHILE @rowcount < 1000
	BEGIN
		SET NOCOUNT ON
		INSERT INTO Target_Tbl
		(ID, Sample_Data_Col1, Sample_Data_Col2, Sample_Data_Col3)
		SELECT 
		@rowcount, 
		'Sample_Data' + CAST(@rowcount as varchar(10)), 
		'Sample_Data' + CAST(@rowcount as varchar(10)), 
		'Sample_Data' + CAST(@rowcount as varchar(10))
		SET @rowcount = @rowcount + 1
	END
GO

DELETE FROM Target_DB.dbo.Target_Tbl
WHERE ID >= 500

UPDATE Source_DB.dbo.Source_Tbl
SET Sample_Data_Col1 = 'Changed_Data'
WHERE ID < 10
UPDATE Source_DB.dbo.Source_Tbl
SET Sample_Data_Col2 = 'Changed_Data'
WHERE ID < 10
UPDATE Source_DB.dbo.Source_Tbl
SET Sample_Data_Col3 = 'Changed_Data'
WHERE ID < 10

CREATE UNIQUE CLUSTERED INDEX [Clustered_Idx_Id] ON Target_DB.dbo.Target_Tbl
([ID] ASC)
GO
CREATE UNIQUE CLUSTERED INDEX [Clustered_Idx_Id] ON Source_DB.dbo.Source_Tbl
([ID] ASC)
GO

SELECT COUNT(1) AS Target_Count FROM Target_DB.dbo.Target_Tbl
SELECT COUNT(1) AS Source_Count FROM Source_DB.dbo.Source_Tbl

SELECT COUNT(1) AS Records_Count_Difference FROM
(SELECT ID FROM Target_DB.dbo.Target_Tbl
INTERSECT
SELECT ID FROM Source_DB.dbo.Source_Tbl) AS Records_Count_Difference
 
SELECT COUNT(1) AS Data_Records_Count_Difference FROM
(SELECT * FROM Target_DB.dbo.Target_Tbl
INTERSECT
SELECT * FROM Source_DB.dbo.Source_Tbl) AS Data_Records_Count_Difference

When the two databases and tables have been created and populated, the Results pane should display the difference status as per image below.

DBMergeSync_Environment_SetUp1

You will notice that the difference for record count between the source and the target is 500 and that out of the data which should be overlapping, 10 records have different values i.e. 490 out of 500 records remained the same whereas 10 records have been changed in the source. This is a very rudimentary scenario and its simplicity is only for demonstration purposes.

Next, let’s assume that we want to synchronise the data between those two tables and databases to account for changed and new records in the target table. Below is the SQL for a stored procedure which accomplishes that, you can also download the SQL for this short series from HERE. As mentioned previously, the code builds MERGE SQL statement on the fly based on database objects’ metadata and as long the table has a primary key constraint present, it automatically handles INSERT and UPDATE based on its content.

USE [Source_DB]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_DBSync]')
AND type IN (N'P',N'PC'))
DROP PROCEDURE [dbo].[usp_DBSync]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[usp_DBSync] 
(@Src_DB			varchar		(100), 
 @Tgt_DB			varchar		(100),
 @Src_Schema_Name	varchar		(10),
 @Tgt_Schema_Name	varchar		(10),
 @Src_Object_Name	varchar		(256),
 @Tgt_Object_Name	varchar		(256)) 
 AS

SET NOCOUNT ON

DECLARE
  @IsDebugMode bit,
  @ExecSQL nvarchar(max),
  @Err_Msg nvarchar (1000)

SET
  @IsDebugMode = 0


/*====================================================================================
								CREATE TEMP TABLES							
======================================================================================*/

CREATE TABLE #Src_Tgt_Tables
(
  [Data_Obj_Id] [int] NOT NULL,
  [Src_Tgt_Flag] [varchar](1) NOT NULL,
  [Object_Id] [int] NOT NULL,
  [Object_Name] [sysname] NOT NULL,
  [Schema_Name] [sysname] NOT NULL,
  [Schema_Object_Name] [varchar](260) NOT NULL,
  [Column_Id] [smallint] NULL,
  [Column_Name] [varchar](200) NULL,
  [IsIdentity] [tinyint] NULL,
  [IsComputed] [tinyint] NULL,
  [IsNullable] [tinyint] NULL,
  [Default] [varchar](max) NULL,
  [DataType] [varchar](152) NULL,
  [DataType_CastGroup] [varchar](134) NOT NULL,
  [Collation_Name] [sysname] NULL
)

CREATE TABLE #Tgt_NK_Cols
(
  [Data_Obj_Id] [int] NOT NULL,
  [Schema_Object_Name] [varchar](260) NOT NULL,
  [Where_Clause] [varchar](max) NULL,
  S_Schema_Object_Name [varchar](260) NOT NULL
)
IF @IsDebugMode = 1
  SELECT 
     [Source DB] = @Src_DB,
     [Target DB] = @Tgt_DB,
	 [Source_Schema_Name] = @Src_Schema_Name,
	 [Target_Schema_Name] = @Tgt_Schema_Name,
	 [Source_Objects_Name] = @Src_Object_Name,
	 [Target_Objects_Name] = @Tgt_Object_Name


/*====================================================================================
				PERFORM DATABASES, SCHEMAS AND OBJECTS CHECKS							
======================================================================================*/

DECLARE @CMD varchar (1024)
CREATE TABLE #Objects_List(
DatabaseName	sysname,
SchemaName		sysname,
ObjectName		sysname)

SET @CMD = 'USE [?]; SELECT DB_NAME() DATABASE_NAME, SCHEMA_NAME(schema_id),
NAME FROM sys.tables'

INSERT INTO #Objects_List
EXEC SP_MSFOREACHDB @CMD

DELETE FROM #Objects_List
WHERE DatabaseName IN ('master', 'msdb', 'tempdb', 'model')

IF NOT EXISTS (SELECT 1 FROM #Objects_List a WHERE a.databasename = @Src_DB)
BEGIN
  SET 
    @Err_Msg = 'Source database cannot be found. You nominated "' + @Src_DB + '". 
				Check that the database of that name exists on the instance' 
    RAISERROR (
        @Err_Msg  -- Message text.
        ,16 -- Severity.
            ,1 -- State.
  )
END    

IF NOT EXISTS (SELECT 1 FROM #Objects_List a WHERE a.databasename = @Tgt_DB)
BEGIN
  SET 
    @Err_Msg = 'Target database cannot be found. You nominated "' + @Tgt_DB + '". 
				Check that the database of that name exists on the instance' 
    RAISERROR (
        @Err_Msg  -- Message text.
        ,16 -- Severity.
            ,1 -- State.
  )
END    

IF NOT EXISTS (SELECT 1 FROM #Objects_List a WHERE a.SchemaName = @Src_Schema_Name)
BEGIN
  SET 
    @Err_Msg = 'Source schema cannot be found. You nominated "' + @Src_Schema_Name + '". 
				Check that the schema of that name exists on the database' 
    RAISERROR (
        @Err_Msg  -- Message text.
        ,16 -- Severity.
            ,1 -- State.
  )
END   

IF NOT EXISTS (SELECT 1 FROM #Objects_List a WHERE a.SchemaName = @Tgt_Schema_Name)
BEGIN
  SET 
    @Err_Msg = 'Target schema cannot be found. You nominated "' + @Tgt_Schema_Name + '". 
				Check that the schema of that name exists on the database' 
    RAISERROR (
        @Err_Msg  -- Message text.
        ,16 -- Severity.
            ,1 -- State.
  )
END  

IF NOT EXISTS (SELECT 1 FROM #Objects_List a WHERE a.ObjectName = @Src_Object_Name)
BEGIN
  SET 
    @Err_Msg = 'Source object cannot be found. You nominated "' + @Src_Object_Name + '". 
				Check that the object of that name exists on the database' 
    RAISERROR (
        @Err_Msg  -- Message text.
        ,16 -- Severity.
            ,1 -- State.
  )
END 

IF NOT EXISTS (SELECT 1 FROM #Objects_List a WHERE a.ObjectName = @Tgt_Object_Name)
BEGIN
  SET 
    @Err_Msg = 'Target object cannot be found. You nominated "' + @Tgt_Object_Name + '". 
				Check that the object of that name exists on the database' 
    RAISERROR (
        @Err_Msg  -- Message text.
        ,16 -- Severity.
            ,1 -- State.
  )
END 


/*====================================================================================
						EXTRACT SOURCE AND TARGET DATA							
======================================================================================*/
SET
   @ExecSQL =
   'INSERT INTO
	#Src_Tgt_Tables
	SELECT
	[Data_Object_Id]		=	so.id,
	[Src_Trg_Flag]			=	src_tgt.Src_Trg_Flag,
	[Object_Id]				=	so.id,
	[Object_Name]			=	so.name,
	[Schema_Name]			=	sh.name,
	[Schema_Object_Name]	=	''[' +@Src_DB+ '].[''+sh.name+''].[''+so.name+'']'',
	[Column_Id]				=	sc.column_id,
	[Column_Name]			=	''[''+sc.name+'']'',
	[IsIdentity]			=	sc.is_identity,
	[IsComputed]			=	sc.is_computed,
	[IsNullable]			=	sc.is_nullable,
	[Default]				=	dc.definition,
	[DataType]				=	(
								CASE
								WHEN T.system_type_id IN (167, 175, 231, 239) AND SC.max_length > 0 
								THEN T.Name + ''('' + CAST(SC.max_length AS varchar(10)) + '')''
								WHEN T.system_type_id IN (167, 175, 231, 239) AND SC.max_length = -1 THEN T.Name + ''(MAX)''
								-- For Numeric and Decimal data types
								WHEN T.system_type_id IN (106, 108) THEN T.Name + ''('' + CAST(SC.precision AS varchar(10)) + '', '' + 
								CAST(SC.scale AS varchar(10)) + '')''
								ELSE T.Name
								END
								),
	[DataType_CastGroup]	=	(
								CASE
								WHEN T.system_type_id IN (167, 175, 231, 239) THEN ''String''
								-- For Numeric and Decimal data types
								WHEN T.system_type_id IN (106, 108) THEN ''Numeric''
								ELSE ''Other''
								END
								),
	[Collation_Name]		=	SC.collation_name

	FROM
	'+@Src_DB+'.sys.sysobjects so (NOLOCK)
	INNER JOIN '+@Src_DB+'.sys.columns sc (NOLOCK) ON
    sc.object_id = so.id
    LEFT JOIN '+@Src_DB+'.sys.default_constraints dc (NOLOCK) ON
    dc.parent_object_id = so.id
    AND dc.parent_column_id = sc.column_id
	INNER JOIN '+@Src_DB+'.sys.types t (NOLOCK) ON
    t.user_type_id = sc.user_type_id
	INNER JOIN '+@Src_DB+'.sys.schemas sh (NOLOCK) ON
    sh.schema_id = so.uid
	INNER  JOIN (
				select  
				Data_Obj_Id = t.object_id, 
				t.name as phisical_name, 
				s.name as s_name  ,
				Src_trg_Flag = ''S'',
				Object_Type = ''U''
				FROM   '+@Src_DB+'.sys.tables t
				JOIN sys.schemas s ON t.schema_id = s.schema_id
				WHERE s.name = '''+@Src_Schema_Name+'''
				and t.name = '''+@Src_Object_Name+'''
				) src_tgt ON
	src_tgt.phisical_name = so.name and
    src_tgt.s_name = sh.name
    WHERE
	so.xtype = src_tgt.Object_Type'
	
IF @IsDebugMode = 1
  PRINT @ExecSQL
  
EXEC sp_executesql @ExecSQL
  
  SET
  @ExecSQL =
	'INSERT INTO
	#Src_Tgt_Tables
	SELECT
	[Data_Obj_Id]			=	src_tgt.Data_Obj_Id,
	[Src_Trg_Flag]			=	src_tgt.Src_Trg_Flag,
	[Object_Id]				=	so.id,
	[Object_Name]			=	so.name,
	[Schema_Name]			=	sh.name,
	[Schema_Object_Name]	=	''[' +@Tgt_DB+ '].[''+sh.name+''].[''+so.name+'']'',
	[Column_Id]				=	sc.column_id,
	[Column_Name]			=	''[''+sc.name+'']'',
	[IsIdentity]			=	sc.is_identity,
	[IsComputed]			=	sc.is_computed,
	[IsNullable]			=	sc.is_nullable,
	[Default]				=	dc.definition,
	[DataType]				=	(
								CASE
								WHEN T.system_type_id IN (167, 175, 231, 239) AND SC.max_length > 0 
								THEN T.Name + ''('' + CAST(SC.max_length AS varchar(10)) + '')''
								WHEN T.system_type_id IN (167, 175, 231, 239) AND SC.max_length = -1 THEN T.Name + ''(MAX)''
								-- For Numeric and Decimal data types
								WHEN T.system_type_id IN (106, 108) THEN T.Name + ''('' + CAST(SC.precision AS varchar(10)) + '', '' 
								+ CAST(SC.scale AS varchar(10)) + '')''
								ELSE T.Name
								END
								),
	[DataType_CastGroup]	=	(
								CASE
								WHEN T.system_type_id IN (167, 175, 231, 239) THEN ''String''
								-- For Numeric and Decimal data types
								WHEN T.system_type_id IN (106, 108) THEN ''Numeric''
								ELSE ''Other''
								END
								),
	[Collation_Name]		=	SC.collation_name
	FROM
	'+@Tgt_DB+'.sys.sysobjects so
	INNER JOIN '+@Tgt_DB+'.sys.columns sc ON
    sc.object_id = so.id
	LEFT JOIN '+@Tgt_DB+'.sys.default_constraints dc ON
    dc.parent_object_id = so.id
    AND dc.parent_column_id = sc.column_id
	INNER JOIN '+@Tgt_DB+'.sys.types t ON
    t.user_type_id = sc.user_type_id
	INNER JOIN '+@Tgt_DB+'.sys.schemas sh ON
    sh.schema_id = so.uid
	INNER JOIN (
				select  
				Data_Obj_Id = t.object_id, 
				t.name as phisical_name, 
				s.name as s_name,
				Src_trg_Flag = ''T'',
				Object_Type = ''U''
				FROM   '+@tgt_DB+'.sys.tables t
				JOIN sys.schemas s ON t.schema_id = s.schema_id
				WHERE S.name = '''+@Tgt_Schema_Name+'''
				and t.name = '''+@Tgt_Object_Name+'''
				) src_tgt ON
	src_tgt.phisical_name = so.name and
    src_tgt.s_name = sh.name
	WHERE
	so.xtype = ''U'' -- Table
	ORDER BY
	so.name,
	sc.column_id'
	
IF @IsDebugMode = 1
  PRINT @ExecSQL
 
EXEC sp_executesql @ExecSQL


/*====================================================================================
		ENSURE THAT SOURCE AND TARGET DETAILS ARE PRESENT IN TEMP TABLE							
======================================================================================*/

IF @IsDebugMode = 1
  SELECT [Table] = '#Src_Tgt_Tables', * FROM #Src_Tgt_Tables
 
IF (SELECT COUNT(*) FROM #Src_Tgt_Tables ST WHERE ST.Src_Tgt_Flag = 'S') < 1
BEGIN
	SET @Err_Msg = 'No Source table details found. Configured Source Database is "' + @Src_DB + '".'
    RAISERROR (
        @Err_Msg  -- Message text.
        ,16 -- Severity.
            ,1 -- State.
  )
END

IF (SELECT COUNT(*) FROM #Src_Tgt_Tables ST WHERE ST.Src_Tgt_Flag = 'T') < 1
BEGIN
	SET @Err_Msg = 'No Target table details found. Configured Source Database is "' + @Tgt_DB + '".'
    RAISERROR (
        @Err_Msg  -- Message text.
        ,16 -- Severity.
            ,1 -- State.
  )
END   


/*====================================================================================
				PREPARE 'WHERE' CLAUSE FOR THE MERGE STATEMENT							
======================================================================================*/

SET
  @ExecSQL = 
	'INSERT INTO
	#Tgt_NK_Cols
	SELECT
	TOP 1
	Data_Obj_Id				=	tgt.Data_Obj_Id,
	[Schema_Object_Name]	=	tgt.[Schema_Object_Name],
	[Where_Clause]			=	STUFF(REPLACE((SELECT
								''  AND'' + '' TGT.[''+ sc.name +''] =
								SRC.[''+ REPLACE(REPLACE(sc.name, ''<'', ''~''), ''>'', ''!'') + '']'' + CHAR(10)
								FROM
								'+@Tgt_DB+'.sys.sysindexkeys sik
								INNER JOIN '+@Tgt_DB+'.sys.syscolumns sc on
								sc.id = sik.id
								and sc.colid = sik.colid
								WHERE
								sik.id = si.object_id
								AND sik.indid = si.index_id
								ORDER BY
								sik.keyno
								FOR XML PATH('''')
								), ''&#x0D;'', ''''), 1, 5, ''''),
	[S_Schema_Object_Name]	=	(
								SELECT	Top 1
								S.Schema_Object_Name
								FROM
								#Src_Tgt_Tables S
								WHERE
								S.Src_Tgt_Flag = ''S'')
	FROM
	'+@Tgt_DB+'.sys.indexes si
	INNER JOIN '+@Tgt_DB+'.sys.sysobjects so ON
    so.id = si.object_id
	INNER JOIN '+@Tgt_DB+'.sys.schemas sh ON
    sh.schema_id = so.uid
	INNER JOIN (
				SELECT
				[Data_Obj_Id],
				[Object_Id],
				[Object_Name],
				[Schema_Name],
				[Schema_Object_Name]
				FROM
				#Src_Tgt_Tables
				WHERE
				Src_Tgt_Flag = ''T''
				) tgt ON
				tgt.[Object_Id] = so.id
	WHERE 
	si.is_unique = 1 /*Only Unique Index*/'
 
IF @IsDebugMode = 1
 PRINT @ExecSQL
 
EXEC sp_executesql @ExecSQL
 
IF @IsDebugMode = 1
  SELECT [Table] = '#Tgt_NK_Cols', * FROM #Tgt_NK_Cols
 

/*====================================================================================
				ENSURE THAT UNIQUE KEY INDEX IS PRESENT						
======================================================================================*/

IF EXISTS(SELECT 1 FROM #Tgt_NK_Cols NK WHERE NK.Where_Clause IS NULL)
BEGIN
      SET
        @Err_Msg = 'No Unique Key Index is found. 
					Configured Source Database is "' + @Tgt_DB + '".'
 
      RAISERROR (
        @Err_Msg  -- Message text.
        ,16 -- Severity.
            ,1 -- State.
  )
END


/*====================================================================================
							PREPARE MERGE STATEMENT							
======================================================================================*/

DECLARE
@MergeSQL		nvarchar(max),
@UpdateColSet	nvarchar(max),
@TargetColSet	nvarchar(max),
@SourceColSet	nvarchar(max),
@ValueColSet	nvarchar(max)

SELECT
@MergeSQL = '
MERGE '+NK.Schema_Object_Name+' TGT
USING (
  SELECT{SOURCE_COLUMN_SET}
  FROM
    '+NK.S_Schema_Object_Name+' SRC (NOLOCK)
  ) SRC ON
 '+ NK.Where_Clause+'
WHEN MATCHED THEN
  UPDATE SET{UPDATE_COLUMN_SET}
WHEN NOT MATCHED THEN
  INSERT({TARGET_COLUMN_SET}
      )
  VALUES ({VALUE_COLUMN_SET}
  )
OUTPUT $action INTO #SummaryOfChanges(Action_Name);
'
FROM
  #Tgt_NK_Cols NK
  SELECT
    @TargetColSet =	REPLACE(STUFF((SELECT ','+CHAR(10) 
					+ '    ' + CAST(TC.Column_Name as varchar(100))
					FROM
					#Src_Tgt_Tables TC (NOLOCK)
					LEFT JOIN (
								SELECT
								T.[Data_Obj_Id]
								,T.[Column_Name]
								,T.[IsNullable]
								,T.[Default]
								,T.[DataType]
								,T.[DataType_CastGroup]
								FROM
								#Src_Tgt_Tables T (NOLOCK)
								WHERE
								T.Src_Tgt_Flag = 'S'
								AND T.Data_Obj_Id = TS.S_Data_Obj_Id
								) SC 
					ON SC.Column_Name = TC.Column_Name
					WHERE
					TC.Src_Tgt_Flag = 'T'
					AND TC.Data_Obj_Id = TS.T_Data_Obj_Id
					--AND TC.IsIdentity <> 1 -- Ignore identity
					AND TC.IsComputed <> 1 -- and computed columns
					ORDER BY
					TC.Column_Id
					FOR XML PATH('')
					), 1, 1, ''), '&#x0D;', ''),

@SourceColSet =		REPLACE(STUFF((SELECT ','+CHAR(10) + '    ' + ISNULL('SRC.' + CAST(SC.Column_Name as varchar(100)), 
					CAST(TC.Column_Name as varchar(100))+' = '+ REPLACE(REPLACE('{'+TC.Column_Name+'}', '{[', '{'), ']}', '}'))
					FROM #Src_Tgt_Tables TC (NOLOCK)
					LEFT JOIN (
								SELECT
								T.[Data_Obj_Id]
								,T.[Column_Name]
								,T.[IsNullable]
								,T.[Default]
								,T.[DataType]
								,T.[DataType_CastGroup]
								FROM
								#Src_Tgt_Tables T (NOLOCK)
								WHERE
								T.Src_Tgt_Flag = 'S'
								AND T.Data_Obj_Id = TS.S_Data_Obj_Id
								) SC 
					ON SC.Column_Name = TC.Column_Name
					WHERE
					TC.Src_Tgt_Flag = 'T'
					AND TC.Data_Obj_Id = TS.T_Data_Obj_Id
					AND TC.IsIdentity <> 1 -- Ignore identity
					AND TC.IsComputed <> 1 -- and computed columns
					ORDER BY
					TC.Column_Id
					FOR XML PATH('')
					), 1, 1, ''), '&#x0D;', ''),

@UpdateColSet =		REPLACE(STUFF((SELECT ','+CHAR(10) + '    ' + CAST(TC.Column_Name as varchar(100))+' = SRC.' + ISNULL(SC.Column_Name, TC.Column_Name)
					FROM
					#Src_Tgt_Tables TC (NOLOCK)
					LEFT JOIN (
								SELECT
								T.[Data_Obj_Id]
								,T.[Column_Name]
								,T.[IsNullable]
								,T.[Default]
								,T.[DataType]
								,T.[DataType_CastGroup]
								FROM
								#Src_Tgt_Tables T (NOLOCK)
								WHERE
								T.Src_Tgt_Flag = 'S'
								AND T.Data_Obj_Id = TS.S_Data_Obj_Id
								) SC ON
					SC.Column_Name = TC.Column_Name
					WHERE
				    TC.Src_Tgt_Flag = 'T'
					AND TC.Data_Obj_Id = TS.T_Data_Obj_Id
					AND TC.IsIdentity <> 1 -- Ignore identity
					AND TC.IsComputed <> 1 -- and computed columns
					ORDER BY
					TC.Column_Id
					FOR XML PATH('')
					), 1, 1, ''), '&#x0D;', ''),
						

@ValueColSet =		REPLACE(STUFF((SELECT ','+CHAR(10) + '    ' + ISNULL('SRC.' + CAST(SC.Column_Name as varchar(100)), 
					'SRC.' + CAST(TC.Column_Name as varchar(100)))
					FROM
					#Src_Tgt_Tables TC (NOLOCK)
					LEFT JOIN (
								SELECT
								T.[Data_Obj_Id]
						        ,T.[Column_Name]
								,T.[IsNullable]
					            ,T.[Default]
								,T.[DataType]
								,T.[DataType_CastGroup]
							    FROM
							    #Src_Tgt_Tables T (NOLOCK)
						        WHERE
							    T.Src_Tgt_Flag = 'S'
							    AND T.Data_Obj_Id = TS.S_Data_Obj_Id
						        ) SC ON
			         SC.Column_Name = TC.Column_Name
				     WHERE
				     TC.Src_Tgt_Flag = 'T'
				     AND TC.Data_Obj_Id = TS.T_Data_Obj_Id
				     AND TC.IsIdentity <> 1 -- Ignore identity
				     AND TC.IsComputed <> 1 -- and computed columns
				     ORDER BY
				     TC.Column_Id
					 FOR XML PATH('')
					), 1, 1, ''), '&#x0D;', '')
FROM
	    (SELECT [T_Data_Obj_Id] = T.Data_Obj_Id,
        [T_Schema_Object_Name] = T.Schema_Object_Name,
        [S_Schema_Object_Name] = (
									SELECT Top 1 S.Schema_Object_Name
									FROM
						            #Src_Tgt_Tables S
							        WHERE
						            S.Src_Tgt_Flag = 'S'
								   ),
        [S_Data_Obj_Id]		= (
						          SELECT Top 1 S.Data_Obj_Id FROM
									#Src_Tgt_Tables S
									WHERE
						            S.Src_Tgt_Flag = 'S'
						          )
	      FROM #Src_Tgt_Tables T
	      WHERE
	      T.Src_Tgt_Flag = 'T'
		) TS

SELECT @MergeSQL = REPLACE(@MergeSQL, '{UPDATE_COLUMN_SET}', @UpdateColSet)
SELECT @MergeSQL = REPLACE(@MergeSQL, '{TARGET_COLUMN_SET}', @TargetColSet)
SELECT @MergeSQL = REPLACE(@MergeSQL, '{SOURCE_COLUMN_SET}', @SourceColSet)
SELECT @MergeSQL = REPLACE(@MergeSQL, '{VALUE_COLUMN_SET}', @ValueColSet)


/*====================================================================================
			EXECUTE MERGE STATEMENT AND CHECK FOR EXECUTION RESULTS							
======================================================================================*/

DECLARE
@UpdatedCount	int,
@InsertedCount	int,
@DeletedCount	int,
@StartTime		datetime,
@EndTime		datetime

CREATE TABLE #SummaryOfChanges (Action_Name VARCHAR(50));
SET @StartTime = GETDATE()

BEGIN TRY
	IF @IsDebugMode = 1
	BEGIN
		PRINT @MergeSQL
	END

		EXEC sp_executesql @MergeSQL

		SELECT
		@UpdatedCount	=	SUM(CASE WHEN Action_Name = 'UPDATE' THEN 1 ELSE 0 END),
		@InsertedCount	=	SUM(CASE WHEN Action_Name = 'INSERT' THEN 1 ELSE 0 END),
		@DeletedCount	=	SUM(CASE WHEN Action_Name = 'DELETE' THEN 1 ELSE 0 END)
		FROM
		#SummaryOfChanges
	
	IF @IsDebugMode = 1
	BEGIN
		SELECT @UpdatedCount as Records_Updated
		SELECT @InsertedCount as Records_Inserted
		SELECT @DeletedCount as Records_Deleted
	END
END TRY
BEGIN CATCH
END CATCH
SET NOCOUNT OFF;

In order to synchronise the data between the two previously created objects let’s run the usp_DBSync stored procedure (assuming you have already executed the above SQL) either from the context menu in Management Studio or by running the following code.

USE [Source_DB]
GO
DECLARE	@return_value int
EXEC	@return_value = [dbo].[usp_DBSync]
		@Src_DB = N'Source_DB',
		@Tgt_DB = N'Target_DB',
		@Src_Schema_Name = N'dbo',
		@Tgt_Schema_Name = N'dbo',
		@Src_Object_Name = N'Source_Tbl',
		@Tgt_Object_Name = N'Target_Tbl'
SELECT	'Return Value' = @return_value
GO

Finally, to check if the data has been synchronised successfully, let’s run the last few lines from the first SQL code batch used to create the databases and objects and compare the results. If everything executed as expected and the source has been merged with the target, the output should be as per below.

DBMergeSync_Merge_AfterExec_Results

One thing to note here is that MERGE does not seem to work across two different SQL Server instances e.g. between linked servers. For that you will need to break/alter the above MERGE SQL into INSERT and UPDATE statements. In the NEXT POST to this series I will show how to synchronise multiple objects across two databases. Rather than executing our MERGE stored procedure for each database object individually, we can provide a looping functionality by means of using another stored procedure or an SSIS package to pick up all relevant object and execute it as many times as there is tables to merge together.

Just in case you have any issues coping and pasting, all SQL code as well as any additional files can be downloaded from HERE.

Submit Article :- BlinkList + Blogmarks + Digg + Del.icio.us + Ekstreme Socializer + Feedmarker + Furl + Google Bookmarks + ma.gnolia + Netvouz + RawSugar + Reddit + Scuttle + Shadows + Simpy + Spurl + Technorati + Unalog + Wink

http://scuttle.org/bookmarks.php/pass?action=add

Posted in: How To's, SQL

Tags:

This entry was posted on Tuesday, September 17th, 2013 at 11:38 pm and is filed under How To's, SQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply