{"id":1816,"date":"2013-09-17T23:38:57","date_gmt":"2013-09-17T23:38:57","guid":{"rendered":"http:\/\/bicortex.com\/?p=1816"},"modified":"2013-09-18T03:46:02","modified_gmt":"2013-09-18T03:46:02","slug":"how-to-synchronise-data-across-two-sql-server-databases-part-1-sql-code-and-application-for-individual-objects-processing","status":"publish","type":"post","link":"http:\/\/bicortex.com\/bicortex\/how-to-synchronise-data-across-two-sql-server-databases-part-1-sql-code-and-application-for-individual-objects-processing\/","title":{"rendered":"How To Synchronise Data Across Two SQL Server Databases – Part 1. SQL Code And Application For Individual Objects Processing"},"content":{"rendered":"

Note: This series is comprised of two posts \u2013 this one and another one which can be viewed HERE<\/b><\/a>.<\/p>\n

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\u2019 metadata and as long the table has a primary key constraint present, it automatically handles INSERT and UPDATE based on its content.<\/p>\n

Let\u2019s 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 \u2013 both inserts and updates. To materialise this scenario on a database level, let\u2019s execute the following SQL and create the above databases, objects and some dummy data.<\/p>\n

\r\nUSE [master]\r\nGO\r\nIF EXISTS (SELECT name FROM sys.databases WHERE name = N'Source_DB')\r\nBEGIN\r\n-- Close connections to the DW_Sample database\r\nALTER DATABASE [Source_DB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE\r\nDROP DATABASE [Source_DB]\r\nEND\r\nGO\r\nCREATE DATABASE [Source_DB] \r\n\r\nIF EXISTS (SELECT name FROM sys.databases WHERE name = N'Target_DB')\r\nBEGIN\r\n-- Close connections to the DW_Sample database\r\nALTER DATABASE [Target_DB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE\r\nDROP DATABASE [Target_DB]\r\nEND\r\nGO\r\nCREATE DATABASE [Target_DB]\r\n\r\nUSE Source_DB\r\nCREATE TABLE Source_Tbl (\r\nID int NOT NULL,\r\nSample_Data_Col1 varchar (50) NOT NULL,\r\nSample_Data_Col2 varchar (50) NOT NULL,\r\nSample_Data_Col3 varchar (50) NOT NULL)\r\nGO\r\n\r\nUSE Target_DB\r\nCREATE TABLE Target_Tbl (\r\nID int NOT NULL,\r\nSample_Data_Col1 varchar (50) NOT NULL,\r\nSample_Data_Col2 varchar (50) NOT NULL,\r\nSample_Data_Col3 varchar (50) NOT NULL)\r\nGO\r\n\r\nUSE Source_DB\r\nDECLARE @rowcount int = 0\r\nWHILE @rowcount < 1000\r\n\tBEGIN\r\n\t\tSET NOCOUNT ON\r\n\t\tINSERT INTO Source_Tbl\r\n\t\t(ID, Sample_Data_Col1, Sample_Data_Col2, Sample_Data_Col3)\r\n\t\tSELECT \r\n\t\t@rowcount, \r\n\t\t'Sample_Data' + CAST(@rowcount as varchar(10)), \r\n\t\t'Sample_Data' + CAST(@rowcount as varchar(10)), \r\n\t\t'Sample_Data' + CAST(@rowcount as varchar(10))\r\n\t\tSET @rowcount = @rowcount + 1\r\n\tEND\r\nGO\r\n\r\nUSE Target_DB\r\nDECLARE @rowcount int = 0\r\nWHILE @rowcount < 1000\r\n\tBEGIN\r\n\t\tSET NOCOUNT ON\r\n\t\tINSERT INTO Target_Tbl\r\n\t\t(ID, Sample_Data_Col1, Sample_Data_Col2, Sample_Data_Col3)\r\n\t\tSELECT \r\n\t\t@rowcount, \r\n\t\t'Sample_Data' + CAST(@rowcount as varchar(10)), \r\n\t\t'Sample_Data' + CAST(@rowcount as varchar(10)), \r\n\t\t'Sample_Data' + CAST(@rowcount as varchar(10))\r\n\t\tSET @rowcount = @rowcount + 1\r\n\tEND\r\nGO\r\n\r\nDELETE FROM Target_DB.dbo.Target_Tbl\r\nWHERE ID >= 500\r\n\r\nUPDATE Source_DB.dbo.Source_Tbl\r\nSET Sample_Data_Col1 = 'Changed_Data'\r\nWHERE ID < 10\r\nUPDATE Source_DB.dbo.Source_Tbl\r\nSET Sample_Data_Col2 = 'Changed_Data'\r\nWHERE ID < 10\r\nUPDATE Source_DB.dbo.Source_Tbl\r\nSET Sample_Data_Col3 = 'Changed_Data'\r\nWHERE ID < 10\r\n\r\nCREATE UNIQUE CLUSTERED INDEX [Clustered_Idx_Id] ON Target_DB.dbo.Target_Tbl\r\n([ID] ASC)\r\nGO\r\nCREATE UNIQUE CLUSTERED INDEX [Clustered_Idx_Id] ON Source_DB.dbo.Source_Tbl\r\n([ID] ASC)\r\nGO\r\n\r\nSELECT COUNT(1) AS Target_Count FROM Target_DB.dbo.Target_Tbl\r\nSELECT COUNT(1) AS Source_Count FROM Source_DB.dbo.Source_Tbl\r\n\r\nSELECT COUNT(1) AS Records_Count_Difference FROM\r\n(SELECT ID FROM Target_DB.dbo.Target_Tbl\r\nINTERSECT\r\nSELECT ID FROM Source_DB.dbo.Source_Tbl) AS Records_Count_Difference\r\n \r\nSELECT COUNT(1) AS Data_Records_Count_Difference FROM\r\n(SELECT * FROM Target_DB.dbo.Target_Tbl\r\nINTERSECT\r\nSELECT * FROM Source_DB.dbo.Source_Tbl) AS Data_Records_Count_Difference\r\n<\/pre>\n
When the two databases and tables have been created and populated, the Results pane should display the difference status as per image below.<\/p>\n
\"DBMergeSync_Environment_SetUp1\"<\/a><\/p>\n
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.<\/p>\n
Next, let\u2019s 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<\/strong><\/a>. As mentioned previously, the code builds MERGE SQL statement on the fly based on database objects\u2019 metadata and as long the table has a primary key constraint present, it automatically handles INSERT and UPDATE based on its content.<\/p>\n
\r\nUSE [Source_DB]\r\nGO\r\nIF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_DBSync]')\r\nAND type IN (N'P',N'PC'))\r\nDROP PROCEDURE [dbo].[usp_DBSync]\r\nGO\r\nSET ANSI_NULLS ON\r\nGO\r\nSET QUOTED_IDENTIFIER OFF\r\nGO\r\n\r\nCREATE PROCEDURE [dbo].[usp_DBSync] \r\n(@Src_DB\t\t\tvarchar\t\t(100), \r\n @Tgt_DB\t\t\tvarchar\t\t(100),\r\n @Src_Schema_Name\tvarchar\t\t(10),\r\n @Tgt_Schema_Name\tvarchar\t\t(10),\r\n @Src_Object_Name\tvarchar\t\t(256),\r\n @Tgt_Object_Name\tvarchar\t\t(256)) \r\n AS\r\n\r\nSET NOCOUNT ON\r\n\r\nDECLARE\r\n  @IsDebugMode bit,\r\n  @ExecSQL nvarchar(max),\r\n  @Err_Msg nvarchar (1000)\r\n\r\nSET\r\n  @IsDebugMode = 0\r\n\r\n\r\n\/*====================================================================================\r\n\t\t\t\t\t\t\t\tCREATE TEMP TABLES\t\t\t\t\t\t\t\r\n======================================================================================*\/\r\n\r\nCREATE TABLE #Src_Tgt_Tables\r\n(\r\n  [Data_Obj_Id] [int] NOT NULL,\r\n  [Src_Tgt_Flag] [varchar](1) NOT NULL,\r\n  [Object_Id] [int] NOT NULL,\r\n  [Object_Name] [sysname] NOT NULL,\r\n  [Schema_Name] [sysname] NOT NULL,\r\n  [Schema_Object_Name] [varchar](260) NOT NULL,\r\n  [Column_Id] [smallint] NULL,\r\n  [Column_Name] [varchar](200) NULL,\r\n  [IsIdentity] [tinyint] NULL,\r\n  [IsComputed] [tinyint] NULL,\r\n  [IsNullable] [tinyint] NULL,\r\n  [Default] [varchar](max) NULL,\r\n  [DataType] [varchar](152) NULL,\r\n  [DataType_CastGroup] [varchar](134) NOT NULL,\r\n  [Collation_Name] [sysname] NULL\r\n)\r\n\r\nCREATE TABLE #Tgt_NK_Cols\r\n(\r\n  [Data_Obj_Id] [int] NOT NULL,\r\n  [Schema_Object_Name] [varchar](260) NOT NULL,\r\n  [Where_Clause] [varchar](max) NULL,\r\n  S_Schema_Object_Name [varchar](260) NOT NULL\r\n)\r\nIF @IsDebugMode = 1\r\n  SELECT \r\n     [Source DB] = @Src_DB,\r\n     [Target DB] = @Tgt_DB,\r\n\t [Source_Schema_Name] = @Src_Schema_Name,\r\n\t [Target_Schema_Name] = @Tgt_Schema_Name,\r\n\t [Source_Objects_Name] = @Src_Object_Name,\r\n\t [Target_Objects_Name] = @Tgt_Object_Name\r\n\r\n\r\n\/*====================================================================================\r\n\t\t\t\tPERFORM DATABASES, SCHEMAS AND OBJECTS CHECKS\t\t\t\t\t\t\t\r\n======================================================================================*\/\r\n\r\nDECLARE @CMD varchar (1024)\r\nCREATE TABLE #Objects_List(\r\nDatabaseName\tsysname,\r\nSchemaName\t\tsysname,\r\nObjectName\t\tsysname)\r\n\r\nSET @CMD = 'USE [?]; SELECT DB_NAME() DATABASE_NAME, SCHEMA_NAME(schema_id),\r\nNAME FROM sys.tables'\r\n\r\nINSERT INTO #Objects_List\r\nEXEC SP_MSFOREACHDB @CMD\r\n\r\nDELETE FROM #Objects_List\r\nWHERE DatabaseName IN ('master', 'msdb', 'tempdb', 'model')\r\n\r\nIF NOT EXISTS (SELECT 1 FROM #Objects_List a WHERE a.databasename = @Src_DB)\r\nBEGIN\r\n  SET \r\n    @Err_Msg = 'Source database cannot be found. You nominated "' + @Src_DB + '". \r\n\t\t\t\tCheck that the database of that name exists on the instance' \r\n    RAISERROR (\r\n        @Err_Msg  -- Message text.\r\n        ,16 -- Severity.\r\n            ,1 -- State.\r\n  )\r\nEND    \r\n\r\nIF NOT EXISTS (SELECT 1 FROM #Objects_List a WHERE a.databasename = @Tgt_DB)\r\nBEGIN\r\n  SET \r\n    @Err_Msg = 'Target database cannot be found. You nominated "' + @Tgt_DB + '". \r\n\t\t\t\tCheck that the database of that name exists on the instance' \r\n    RAISERROR (\r\n        @Err_Msg  -- Message text.\r\n        ,16 -- Severity.\r\n            ,1 -- State.\r\n  )\r\nEND    \r\n\r\nIF NOT EXISTS (SELECT 1 FROM #Objects_List a WHERE a.SchemaName = @Src_Schema_Name)\r\nBEGIN\r\n  SET \r\n    @Err_Msg = 'Source schema cannot be found. You nominated "' + @Src_Schema_Name + '". \r\n\t\t\t\tCheck that the schema of that name exists on the database' \r\n    RAISERROR (\r\n        @Err_Msg  -- Message text.\r\n        ,16 -- Severity.\r\n            ,1 -- State.\r\n  )\r\nEND   \r\n\r\nIF NOT EXISTS (SELECT 1 FROM #Objects_List a WHERE a.SchemaName = @Tgt_Schema_Name)\r\nBEGIN\r\n  SET \r\n    @Err_Msg = 'Target schema cannot be found. You nominated "' + @Tgt_Schema_Name + '". \r\n\t\t\t\tCheck that the schema of that name exists on the database' \r\n    RAISERROR (\r\n        @Err_Msg  -- Message text.\r\n        ,16 -- Severity.\r\n            ,1 -- State.\r\n  )\r\nEND  \r\n\r\nIF NOT EXISTS (SELECT 1 FROM #Objects_List a WHERE a.ObjectName = @Src_Object_Name)\r\nBEGIN\r\n  SET \r\n    @Err_Msg = 'Source object cannot be found. You nominated "' + @Src_Object_Name + '". \r\n\t\t\t\tCheck that the object of that name exists on the database' \r\n    RAISERROR (\r\n        @Err_Msg  -- Message text.\r\n        ,16 -- Severity.\r\n            ,1 -- State.\r\n  )\r\nEND \r\n\r\nIF NOT EXISTS (SELECT 1 FROM #Objects_List a WHERE a.ObjectName = @Tgt_Object_Name)\r\nBEGIN\r\n  SET \r\n    @Err_Msg = 'Target object cannot be found. You nominated "' + @Tgt_Object_Name + '". \r\n\t\t\t\tCheck that the object of that name exists on the database' \r\n    RAISERROR (\r\n        @Err_Msg  -- Message text.\r\n        ,16 -- Severity.\r\n            ,1 -- State.\r\n  )\r\nEND \r\n\r\n\r\n\/*====================================================================================\r\n\t\t\t\t\t\tEXTRACT SOURCE AND TARGET DATA\t\t\t\t\t\t\t\r\n======================================================================================*\/\r\nSET\r\n   @ExecSQL =\r\n   'INSERT INTO\r\n\t#Src_Tgt_Tables\r\n\tSELECT\r\n\t[Data_Object_Id]\t\t=\tso.id,\r\n\t[Src_Trg_Flag]\t\t\t=\tsrc_tgt.Src_Trg_Flag,\r\n\t[Object_Id]\t\t\t\t=\tso.id,\r\n\t[Object_Name]\t\t\t=\tso.name,\r\n\t[Schema_Name]\t\t\t=\tsh.name,\r\n\t[Schema_Object_Name]\t=\t''[' +@Src_DB+ '].[''+sh.name+''].[''+so.name+'']'',\r\n\t[Column_Id]\t\t\t\t=\tsc.column_id,\r\n\t[Column_Name]\t\t\t=\t''[''+sc.name+'']'',\r\n\t[IsIdentity]\t\t\t=\tsc.is_identity,\r\n\t[IsComputed]\t\t\t=\tsc.is_computed,\r\n\t[IsNullable]\t\t\t=\tsc.is_nullable,\r\n\t[Default]\t\t\t\t=\tdc.definition,\r\n\t[DataType]\t\t\t\t=\t(\r\n\t\t\t\t\t\t\t\tCASE\r\n\t\t\t\t\t\t\t\tWHEN T.system_type_id IN (167, 175, 231, 239) AND SC.max_length > 0 \r\n\t\t\t\t\t\t\t\tTHEN T.Name + ''('' + CAST(SC.max_length AS varchar(10)) + '')''\r\n\t\t\t\t\t\t\t\tWHEN T.system_type_id IN (167, 175, 231, 239) AND SC.max_length = -1 THEN T.Name + ''(MAX)''\r\n\t\t\t\t\t\t\t\t-- For Numeric and Decimal data types\r\n\t\t\t\t\t\t\t\tWHEN T.system_type_id IN (106, 108) THEN T.Name + ''('' + CAST(SC.precision AS varchar(10)) + '', '' + \r\n\t\t\t\t\t\t\t\tCAST(SC.scale AS varchar(10)) + '')''\r\n\t\t\t\t\t\t\t\tELSE T.Name\r\n\t\t\t\t\t\t\t\tEND\r\n\t\t\t\t\t\t\t\t),\r\n\t[DataType_CastGroup]\t=\t(\r\n\t\t\t\t\t\t\t\tCASE\r\n\t\t\t\t\t\t\t\tWHEN T.system_type_id IN (167, 175, 231, 239) THEN ''String''\r\n\t\t\t\t\t\t\t\t-- For Numeric and Decimal data types\r\n\t\t\t\t\t\t\t\tWHEN T.system_type_id IN (106, 108) THEN ''Numeric''\r\n\t\t\t\t\t\t\t\tELSE ''Other''\r\n\t\t\t\t\t\t\t\tEND\r\n\t\t\t\t\t\t\t\t),\r\n\t[Collation_Name]\t\t=\tSC.collation_name\r\n\r\n\tFROM\r\n\t'+@Src_DB+'.sys.sysobjects so (NOLOCK)\r\n\tINNER JOIN '+@Src_DB+'.sys.columns sc (NOLOCK) ON\r\n    sc.object_id = so.id\r\n    LEFT JOIN '+@Src_DB+'.sys.default_constraints dc (NOLOCK) ON\r\n    dc.parent_object_id = so.id\r\n    AND dc.parent_column_id = sc.column_id\r\n\tINNER JOIN '+@Src_DB+'.sys.types t (NOLOCK) ON\r\n    t.user_type_id = sc.user_type_id\r\n\tINNER JOIN '+@Src_DB+'.sys.schemas sh (NOLOCK) ON\r\n    sh.schema_id = so.uid\r\n\tINNER  JOIN (\r\n\t\t\t\tselect  \r\n\t\t\t\tData_Obj_Id = t.object_id, \r\n\t\t\t\tt.name as phisical_name, \r\n\t\t\t\ts.name as s_name  ,\r\n\t\t\t\tSrc_trg_Flag = ''S'',\r\n\t\t\t\tObject_Type = ''U''\r\n\t\t\t\tFROM   '+@Src_DB+'.sys.tables t\r\n\t\t\t\tJOIN sys.schemas s ON t.schema_id = s.schema_id\r\n\t\t\t\tWHERE s.name = '''+@Src_Schema_Name+'''\r\n\t\t\t\tand t.name = '''+@Src_Object_Name+'''\r\n\t\t\t\t) src_tgt ON\r\n\tsrc_tgt.phisical_name = so.name and\r\n    src_tgt.s_name = sh.name\r\n    WHERE\r\n\tso.xtype = src_tgt.Object_Type'\r\n\t\r\nIF @IsDebugMode = 1\r\n  PRINT @ExecSQL\r\n  \r\nEXEC sp_executesql @ExecSQL\r\n  \r\n  SET\r\n  @ExecSQL =\r\n\t'INSERT INTO\r\n\t#Src_Tgt_Tables\r\n\tSELECT\r\n\t[Data_Obj_Id]\t\t\t=\tsrc_tgt.Data_Obj_Id,\r\n\t[Src_Trg_Flag]\t\t\t=\tsrc_tgt.Src_Trg_Flag,\r\n\t[Object_Id]\t\t\t\t=\tso.id,\r\n\t[Object_Name]\t\t\t=\tso.name,\r\n\t[Schema_Name]\t\t\t=\tsh.name,\r\n\t[Schema_Object_Name]\t=\t''[' +@Tgt_DB+ '].[''+sh.name+''].[''+so.name+'']'',\r\n\t[Column_Id]\t\t\t\t=\tsc.column_id,\r\n\t[Column_Name]\t\t\t=\t''[''+sc.name+'']'',\r\n\t[IsIdentity]\t\t\t=\tsc.is_identity,\r\n\t[IsComputed]\t\t\t=\tsc.is_computed,\r\n\t[IsNullable]\t\t\t=\tsc.is_nullable,\r\n\t[Default]\t\t\t\t=\tdc.definition,\r\n\t[DataType]\t\t\t\t=\t(\r\n\t\t\t\t\t\t\t\tCASE\r\n\t\t\t\t\t\t\t\tWHEN T.system_type_id IN (167, 175, 231, 239) AND SC.max_length > 0 \r\n\t\t\t\t\t\t\t\tTHEN T.Name + ''('' + CAST(SC.max_length AS varchar(10)) + '')''\r\n\t\t\t\t\t\t\t\tWHEN T.system_type_id IN (167, 175, 231, 239) AND SC.max_length = -1 THEN T.Name + ''(MAX)''\r\n\t\t\t\t\t\t\t\t-- For Numeric and Decimal data types\r\n\t\t\t\t\t\t\t\tWHEN T.system_type_id IN (106, 108) THEN T.Name + ''('' + CAST(SC.precision AS varchar(10)) + '', '' \r\n\t\t\t\t\t\t\t\t+ CAST(SC.scale AS varchar(10)) + '')''\r\n\t\t\t\t\t\t\t\tELSE T.Name\r\n\t\t\t\t\t\t\t\tEND\r\n\t\t\t\t\t\t\t\t),\r\n\t[DataType_CastGroup]\t=\t(\r\n\t\t\t\t\t\t\t\tCASE\r\n\t\t\t\t\t\t\t\tWHEN T.system_type_id IN (167, 175, 231, 239) THEN ''String''\r\n\t\t\t\t\t\t\t\t-- For Numeric and Decimal data types\r\n\t\t\t\t\t\t\t\tWHEN T.system_type_id IN (106, 108) THEN ''Numeric''\r\n\t\t\t\t\t\t\t\tELSE ''Other''\r\n\t\t\t\t\t\t\t\tEND\r\n\t\t\t\t\t\t\t\t),\r\n\t[Collation_Name]\t\t=\tSC.collation_name\r\n\tFROM\r\n\t'+@Tgt_DB+'.sys.sysobjects so\r\n\tINNER JOIN '+@Tgt_DB+'.sys.columns sc ON\r\n    sc.object_id = so.id\r\n\tLEFT JOIN '+@Tgt_DB+'.sys.default_constraints dc ON\r\n    dc.parent_object_id = so.id\r\n    AND dc.parent_column_id = sc.column_id\r\n\tINNER JOIN '+@Tgt_DB+'.sys.types t ON\r\n    t.user_type_id = sc.user_type_id\r\n\tINNER JOIN '+@Tgt_DB+'.sys.schemas sh ON\r\n    sh.schema_id = so.uid\r\n\tINNER JOIN (\r\n\t\t\t\tselect  \r\n\t\t\t\tData_Obj_Id = t.object_id, \r\n\t\t\t\tt.name as phisical_name, \r\n\t\t\t\ts.name as s_name,\r\n\t\t\t\tSrc_trg_Flag = ''T'',\r\n\t\t\t\tObject_Type = ''U''\r\n\t\t\t\tFROM   '+@tgt_DB+'.sys.tables t\r\n\t\t\t\tJOIN sys.schemas s ON t.schema_id = s.schema_id\r\n\t\t\t\tWHERE S.name = '''+@Tgt_Schema_Name+'''\r\n\t\t\t\tand t.name = '''+@Tgt_Object_Name+'''\r\n\t\t\t\t) src_tgt ON\r\n\tsrc_tgt.phisical_name = so.name and\r\n    src_tgt.s_name = sh.name\r\n\tWHERE\r\n\tso.xtype = ''U'' -- Table\r\n\tORDER BY\r\n\tso.name,\r\n\tsc.column_id'\r\n\t\r\nIF @IsDebugMode = 1\r\n  PRINT @ExecSQL\r\n \r\nEXEC sp_executesql @ExecSQL\r\n\r\n\r\n\/*====================================================================================\r\n\t\tENSURE THAT SOURCE AND TARGET DETAILS ARE PRESENT IN TEMP TABLE\t\t\t\t\t\t\t\r\n======================================================================================*\/\r\n\r\nIF @IsDebugMode = 1\r\n  SELECT [Table] = '#Src_Tgt_Tables', * FROM #Src_Tgt_Tables\r\n \r\nIF (SELECT COUNT(*) FROM #Src_Tgt_Tables ST WHERE ST.Src_Tgt_Flag = 'S') < 1\r\nBEGIN\r\n\tSET @Err_Msg = 'No Source table details found. Configured Source Database is "' + @Src_DB + '".'\r\n    RAISERROR (\r\n        @Err_Msg  -- Message text.\r\n        ,16 -- Severity.\r\n            ,1 -- State.\r\n  )\r\nEND\r\n\r\nIF (SELECT COUNT(*) FROM #Src_Tgt_Tables ST WHERE ST.Src_Tgt_Flag = 'T') < 1\r\nBEGIN\r\n\tSET @Err_Msg = 'No Target table details found. Configured Source Database is "' + @Tgt_DB + '".'\r\n    RAISERROR (\r\n        @Err_Msg  -- Message text.\r\n        ,16 -- Severity.\r\n            ,1 -- State.\r\n  )\r\nEND   \r\n\r\n\r\n\/*====================================================================================\r\n\t\t\t\tPREPARE 'WHERE' CLAUSE FOR THE MERGE STATEMENT\t\t\t\t\t\t\t\r\n======================================================================================*\/\r\n\r\nSET\r\n  @ExecSQL = \r\n\t'INSERT INTO\r\n\t#Tgt_NK_Cols\r\n\tSELECT\r\n\tTOP 1\r\n\tData_Obj_Id\t\t\t\t=\ttgt.Data_Obj_Id,\r\n\t[Schema_Object_Name]\t=\ttgt.[Schema_Object_Name],\r\n\t[Where_Clause]\t\t\t=\tSTUFF(REPLACE((SELECT\r\n\t\t\t\t\t\t\t\t''  AND'' + '' TGT.[''+ sc.name +''] =\r\n\t\t\t\t\t\t\t\tSRC.[''+ REPLACE(REPLACE(sc.name, ''<'', ''~''), ''>'', ''!'') + '']'' + CHAR(10)\r\n\t\t\t\t\t\t\t\tFROM\r\n\t\t\t\t\t\t\t\t'+@Tgt_DB+'.sys.sysindexkeys sik\r\n\t\t\t\t\t\t\t\tINNER JOIN '+@Tgt_DB+'.sys.syscolumns sc on\r\n\t\t\t\t\t\t\t\tsc.id = sik.id\r\n\t\t\t\t\t\t\t\tand sc.colid = sik.colid\r\n\t\t\t\t\t\t\t\tWHERE\r\n\t\t\t\t\t\t\t\tsik.id = si.object_id\r\n\t\t\t\t\t\t\t\tAND sik.indid = si.index_id\r\n\t\t\t\t\t\t\t\tORDER BY\r\n\t\t\t\t\t\t\t\tsik.keyno\r\n\t\t\t\t\t\t\t\tFOR XML PATH('''')\r\n\t\t\t\t\t\t\t\t), ''&#x0D;'', ''''), 1, 5, ''''),\r\n\t[S_Schema_Object_Name]\t=\t(\r\n\t\t\t\t\t\t\t\tSELECT\tTop 1\r\n\t\t\t\t\t\t\t\tS.Schema_Object_Name\r\n\t\t\t\t\t\t\t\tFROM\r\n\t\t\t\t\t\t\t\t#Src_Tgt_Tables S\r\n\t\t\t\t\t\t\t\tWHERE\r\n\t\t\t\t\t\t\t\tS.Src_Tgt_Flag = ''S'')\r\n\tFROM\r\n\t'+@Tgt_DB+'.sys.indexes si\r\n\tINNER JOIN '+@Tgt_DB+'.sys.sysobjects so ON\r\n    so.id = si.object_id\r\n\tINNER JOIN '+@Tgt_DB+'.sys.schemas sh ON\r\n    sh.schema_id = so.uid\r\n\tINNER JOIN (\r\n\t\t\t\tSELECT\r\n\t\t\t\t[Data_Obj_Id],\r\n\t\t\t\t[Object_Id],\r\n\t\t\t\t[Object_Name],\r\n\t\t\t\t[Schema_Name],\r\n\t\t\t\t[Schema_Object_Name]\r\n\t\t\t\tFROM\r\n\t\t\t\t#Src_Tgt_Tables\r\n\t\t\t\tWHERE\r\n\t\t\t\tSrc_Tgt_Flag = ''T''\r\n\t\t\t\t) tgt ON\r\n\t\t\t\ttgt.[Object_Id] = so.id\r\n\tWHERE \r\n\tsi.is_unique = 1 \/*Only Unique Index*\/'\r\n \r\nIF @IsDebugMode = 1\r\n PRINT @ExecSQL\r\n \r\nEXEC sp_executesql @ExecSQL\r\n \r\nIF @IsDebugMode = 1\r\n  SELECT [Table] = '#Tgt_NK_Cols', * FROM #Tgt_NK_Cols\r\n \r\n\r\n\/*====================================================================================\r\n\t\t\t\tENSURE THAT UNIQUE KEY INDEX IS PRESENT\t\t\t\t\t\t\r\n======================================================================================*\/\r\n\r\nIF EXISTS(SELECT 1 FROM #Tgt_NK_Cols NK WHERE NK.Where_Clause IS NULL)\r\nBEGIN\r\n      SET\r\n        @Err_Msg = 'No Unique Key Index is found. \r\n\t\t\t\t\tConfigured Source Database is "' + @Tgt_DB + '".'\r\n \r\n      RAISERROR (\r\n        @Err_Msg  -- Message text.\r\n        ,16 -- Severity.\r\n            ,1 -- State.\r\n  )\r\nEND\r\n\r\n\r\n\/*====================================================================================\r\n\t\t\t\t\t\t\tPREPARE MERGE STATEMENT\t\t\t\t\t\t\t\r\n======================================================================================*\/\r\n\r\nDECLARE\r\n@MergeSQL\t\tnvarchar(max),\r\n@UpdateColSet\tnvarchar(max),\r\n@TargetColSet\tnvarchar(max),\r\n@SourceColSet\tnvarchar(max),\r\n@ValueColSet\tnvarchar(max)\r\n\r\nSELECT\r\n@MergeSQL = '\r\nMERGE '+NK.Schema_Object_Name+' TGT\r\nUSING (\r\n  SELECT{SOURCE_COLUMN_SET}\r\n  FROM\r\n    '+NK.S_Schema_Object_Name+' SRC (NOLOCK)\r\n  ) SRC ON\r\n '+ NK.Where_Clause+'\r\nWHEN MATCHED THEN\r\n  UPDATE SET{UPDATE_COLUMN_SET}\r\nWHEN NOT MATCHED THEN\r\n  INSERT({TARGET_COLUMN_SET}\r\n      )\r\n  VALUES ({VALUE_COLUMN_SET}\r\n  )\r\nOUTPUT $action INTO #SummaryOfChanges(Action_Name);\r\n'\r\nFROM\r\n  #Tgt_NK_Cols NK\r\n  SELECT\r\n    @TargetColSet =\tREPLACE(STUFF((SELECT ','+CHAR(10) \r\n\t\t\t\t\t+ '    ' + CAST(TC.Column_Name as varchar(100))\r\n\t\t\t\t\tFROM\r\n\t\t\t\t\t#Src_Tgt_Tables TC (NOLOCK)\r\n\t\t\t\t\tLEFT JOIN (\r\n\t\t\t\t\t\t\t\tSELECT\r\n\t\t\t\t\t\t\t\tT.[Data_Obj_Id]\r\n\t\t\t\t\t\t\t\t,T.[Column_Name]\r\n\t\t\t\t\t\t\t\t,T.[IsNullable]\r\n\t\t\t\t\t\t\t\t,T.[Default]\r\n\t\t\t\t\t\t\t\t,T.[DataType]\r\n\t\t\t\t\t\t\t\t,T.[DataType_CastGroup]\r\n\t\t\t\t\t\t\t\tFROM\r\n\t\t\t\t\t\t\t\t#Src_Tgt_Tables T (NOLOCK)\r\n\t\t\t\t\t\t\t\tWHERE\r\n\t\t\t\t\t\t\t\tT.Src_Tgt_Flag = 'S'\r\n\t\t\t\t\t\t\t\tAND T.Data_Obj_Id = TS.S_Data_Obj_Id\r\n\t\t\t\t\t\t\t\t) SC \r\n\t\t\t\t\tON SC.Column_Name = TC.Column_Name\r\n\t\t\t\t\tWHERE\r\n\t\t\t\t\tTC.Src_Tgt_Flag = 'T'\r\n\t\t\t\t\tAND TC.Data_Obj_Id = TS.T_Data_Obj_Id\r\n\t\t\t\t\t--AND TC.IsIdentity <> 1 -- Ignore identity\r\n\t\t\t\t\tAND TC.IsComputed <> 1 -- and computed columns\r\n\t\t\t\t\tORDER BY\r\n\t\t\t\t\tTC.Column_Id\r\n\t\t\t\t\tFOR XML PATH('')\r\n\t\t\t\t\t), 1, 1, ''), '&#x0D;', ''),\r\n\r\n@SourceColSet =\t\tREPLACE(STUFF((SELECT ','+CHAR(10) + '    ' + ISNULL('SRC.' + CAST(SC.Column_Name as varchar(100)), \r\n\t\t\t\t\tCAST(TC.Column_Name as varchar(100))+' = '+ REPLACE(REPLACE('{'+TC.Column_Name+'}', '{[', '{'), ']}', '}'))\r\n\t\t\t\t\tFROM #Src_Tgt_Tables TC (NOLOCK)\r\n\t\t\t\t\tLEFT JOIN (\r\n\t\t\t\t\t\t\t\tSELECT\r\n\t\t\t\t\t\t\t\tT.[Data_Obj_Id]\r\n\t\t\t\t\t\t\t\t,T.[Column_Name]\r\n\t\t\t\t\t\t\t\t,T.[IsNullable]\r\n\t\t\t\t\t\t\t\t,T.[Default]\r\n\t\t\t\t\t\t\t\t,T.[DataType]\r\n\t\t\t\t\t\t\t\t,T.[DataType_CastGroup]\r\n\t\t\t\t\t\t\t\tFROM\r\n\t\t\t\t\t\t\t\t#Src_Tgt_Tables T (NOLOCK)\r\n\t\t\t\t\t\t\t\tWHERE\r\n\t\t\t\t\t\t\t\tT.Src_Tgt_Flag = 'S'\r\n\t\t\t\t\t\t\t\tAND T.Data_Obj_Id = TS.S_Data_Obj_Id\r\n\t\t\t\t\t\t\t\t) SC \r\n\t\t\t\t\tON SC.Column_Name = TC.Column_Name\r\n\t\t\t\t\tWHERE\r\n\t\t\t\t\tTC.Src_Tgt_Flag = 'T'\r\n\t\t\t\t\tAND TC.Data_Obj_Id = TS.T_Data_Obj_Id\r\n\t\t\t\t\tAND TC.IsIdentity <> 1 -- Ignore identity\r\n\t\t\t\t\tAND TC.IsComputed <> 1 -- and computed columns\r\n\t\t\t\t\tORDER BY\r\n\t\t\t\t\tTC.Column_Id\r\n\t\t\t\t\tFOR XML PATH('')\r\n\t\t\t\t\t), 1, 1, ''), '&#x0D;', ''),\r\n\r\n@UpdateColSet =\t\tREPLACE(STUFF((SELECT ','+CHAR(10) + '    ' + CAST(TC.Column_Name as varchar(100))+' = SRC.' + ISNULL(SC.Column_Name, TC.Column_Name)\r\n\t\t\t\t\tFROM\r\n\t\t\t\t\t#Src_Tgt_Tables TC (NOLOCK)\r\n\t\t\t\t\tLEFT JOIN (\r\n\t\t\t\t\t\t\t\tSELECT\r\n\t\t\t\t\t\t\t\tT.[Data_Obj_Id]\r\n\t\t\t\t\t\t\t\t,T.[Column_Name]\r\n\t\t\t\t\t\t\t\t,T.[IsNullable]\r\n\t\t\t\t\t\t\t\t,T.[Default]\r\n\t\t\t\t\t\t\t\t,T.[DataType]\r\n\t\t\t\t\t\t\t\t,T.[DataType_CastGroup]\r\n\t\t\t\t\t\t\t\tFROM\r\n\t\t\t\t\t\t\t\t#Src_Tgt_Tables T (NOLOCK)\r\n\t\t\t\t\t\t\t\tWHERE\r\n\t\t\t\t\t\t\t\tT.Src_Tgt_Flag = 'S'\r\n\t\t\t\t\t\t\t\tAND T.Data_Obj_Id = TS.S_Data_Obj_Id\r\n\t\t\t\t\t\t\t\t) SC ON\r\n\t\t\t\t\tSC.Column_Name = TC.Column_Name\r\n\t\t\t\t\tWHERE\r\n\t\t\t\t    TC.Src_Tgt_Flag = 'T'\r\n\t\t\t\t\tAND TC.Data_Obj_Id = TS.T_Data_Obj_Id\r\n\t\t\t\t\tAND TC.IsIdentity <> 1 -- Ignore identity\r\n\t\t\t\t\tAND TC.IsComputed <> 1 -- and computed columns\r\n\t\t\t\t\tORDER BY\r\n\t\t\t\t\tTC.Column_Id\r\n\t\t\t\t\tFOR XML PATH('')\r\n\t\t\t\t\t), 1, 1, ''), '&#x0D;', ''),\r\n\t\t\t\t\t\t\r\n\r\n@ValueColSet =\t\tREPLACE(STUFF((SELECT ','+CHAR(10) + '    ' + ISNULL('SRC.' + CAST(SC.Column_Name as varchar(100)), \r\n\t\t\t\t\t'SRC.' + CAST(TC.Column_Name as varchar(100)))\r\n\t\t\t\t\tFROM\r\n\t\t\t\t\t#Src_Tgt_Tables TC (NOLOCK)\r\n\t\t\t\t\tLEFT JOIN (\r\n\t\t\t\t\t\t\t\tSELECT\r\n\t\t\t\t\t\t\t\tT.[Data_Obj_Id]\r\n\t\t\t\t\t\t        ,T.[Column_Name]\r\n\t\t\t\t\t\t\t\t,T.[IsNullable]\r\n\t\t\t\t\t            ,T.[Default]\r\n\t\t\t\t\t\t\t\t,T.[DataType]\r\n\t\t\t\t\t\t\t\t,T.[DataType_CastGroup]\r\n\t\t\t\t\t\t\t    FROM\r\n\t\t\t\t\t\t\t    #Src_Tgt_Tables T (NOLOCK)\r\n\t\t\t\t\t\t        WHERE\r\n\t\t\t\t\t\t\t    T.Src_Tgt_Flag = 'S'\r\n\t\t\t\t\t\t\t    AND T.Data_Obj_Id = TS.S_Data_Obj_Id\r\n\t\t\t\t\t\t        ) SC ON\r\n\t\t\t         SC.Column_Name = TC.Column_Name\r\n\t\t\t\t     WHERE\r\n\t\t\t\t     TC.Src_Tgt_Flag = 'T'\r\n\t\t\t\t     AND TC.Data_Obj_Id = TS.T_Data_Obj_Id\r\n\t\t\t\t     AND TC.IsIdentity <> 1 -- Ignore identity\r\n\t\t\t\t     AND TC.IsComputed <> 1 -- and computed columns\r\n\t\t\t\t     ORDER BY\r\n\t\t\t\t     TC.Column_Id\r\n\t\t\t\t\t FOR XML PATH('')\r\n\t\t\t\t\t), 1, 1, ''), '&#x0D;', '')\r\nFROM\r\n\t    (SELECT [T_Data_Obj_Id] = T.Data_Obj_Id,\r\n        [T_Schema_Object_Name] = T.Schema_Object_Name,\r\n        [S_Schema_Object_Name] = (\r\n\t\t\t\t\t\t\t\t\tSELECT Top 1 S.Schema_Object_Name\r\n\t\t\t\t\t\t\t\t\tFROM\r\n\t\t\t\t\t\t            #Src_Tgt_Tables S\r\n\t\t\t\t\t\t\t        WHERE\r\n\t\t\t\t\t\t            S.Src_Tgt_Flag = 'S'\r\n\t\t\t\t\t\t\t\t   ),\r\n        [S_Data_Obj_Id]\t\t= (\r\n\t\t\t\t\t\t          SELECT Top 1 S.Data_Obj_Id FROM\r\n\t\t\t\t\t\t\t\t\t#Src_Tgt_Tables S\r\n\t\t\t\t\t\t\t\t\tWHERE\r\n\t\t\t\t\t\t            S.Src_Tgt_Flag = 'S'\r\n\t\t\t\t\t\t          )\r\n\t      FROM #Src_Tgt_Tables T\r\n\t      WHERE\r\n\t      T.Src_Tgt_Flag = 'T'\r\n\t\t) TS\r\n\r\nSELECT @MergeSQL = REPLACE(@MergeSQL, '{UPDATE_COLUMN_SET}', @UpdateColSet)\r\nSELECT @MergeSQL = REPLACE(@MergeSQL, '{TARGET_COLUMN_SET}', @TargetColSet)\r\nSELECT @MergeSQL = REPLACE(@MergeSQL, '{SOURCE_COLUMN_SET}', @SourceColSet)\r\nSELECT @MergeSQL = REPLACE(@MergeSQL, '{VALUE_COLUMN_SET}', @ValueColSet)\r\n\r\n\r\n\/*====================================================================================\r\n\t\t\tEXECUTE MERGE STATEMENT AND CHECK FOR EXECUTION RESULTS\t\t\t\t\t\t\t\r\n======================================================================================*\/\r\n\r\nDECLARE\r\n@UpdatedCount\tint,\r\n@InsertedCount\tint,\r\n@DeletedCount\tint,\r\n@StartTime\t\tdatetime,\r\n@EndTime\t\tdatetime\r\n\r\nCREATE TABLE #SummaryOfChanges (Action_Name VARCHAR(50));\r\nSET @StartTime = GETDATE()\r\n\r\nBEGIN TRY\r\n\tIF @IsDebugMode = 1\r\n\tBEGIN\r\n\t\tPRINT @MergeSQL\r\n\tEND\r\n\r\n\t\tEXEC sp_executesql @MergeSQL\r\n\r\n\t\tSELECT\r\n\t\t@UpdatedCount\t=\tSUM(CASE WHEN Action_Name = 'UPDATE' THEN 1 ELSE 0 END),\r\n\t\t@InsertedCount\t=\tSUM(CASE WHEN Action_Name = 'INSERT' THEN 1 ELSE 0 END),\r\n\t\t@DeletedCount\t=\tSUM(CASE WHEN Action_Name = 'DELETE' THEN 1 ELSE 0 END)\r\n\t\tFROM\r\n\t\t#SummaryOfChanges\r\n\t\r\n\tIF @IsDebugMode = 1\r\n\tBEGIN\r\n\t\tSELECT @UpdatedCount as Records_Updated\r\n\t\tSELECT @InsertedCount as Records_Inserted\r\n\t\tSELECT @DeletedCount as Records_Deleted\r\n\tEND\r\nEND TRY\r\nBEGIN CATCH\r\nEND CATCH\r\nSET NOCOUNT OFF;\r\n<\/pre>\n
In order to synchronise the data between the two previously created objects let\u2019s 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.<\/p>\n
\r\nUSE [Source_DB]\r\nGO\r\nDECLARE\t@return_value int\r\nEXEC\t@return_value = [dbo].[usp_DBSync]\r\n\t\t@Src_DB = N'Source_DB',\r\n\t\t@Tgt_DB = N'Target_DB',\r\n\t\t@Src_Schema_Name = N'dbo',\r\n\t\t@Tgt_Schema_Name = N'dbo',\r\n\t\t@Src_Object_Name = N'Source_Tbl',\r\n\t\t@Tgt_Object_Name = N'Target_Tbl'\r\nSELECT\t'Return Value' = @return_value\r\nGO\r\n<\/pre>\n
Finally, to check if the data has been synchronised successfully, let\u2019s 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.<\/p>\n
\"DBMergeSync_Merge_AfterExec_Results\"<\/a><\/p>\n
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<\/b><\/a> 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.<\/p>\n
Just in case you have any issues coping and pasting, all SQL code as well as any additional files can be downloaded from HERE<\/strong><\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"
Note: This series is comprised of two posts \u2013 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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[32,5],"tags":[49],"class_list":["post-1816","post","type-post","status-publish","format-standard","hentry","category-how-tos","category-sql","tag-sql"],"aioseo_notices":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/1816","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/comments?post=1816"}],"version-history":[{"count":19,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/1816\/revisions"}],"predecessor-version":[{"id":1862,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/1816\/revisions\/1862"}],"wp:attachment":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/media?parent=1816"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/categories?post=1816"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/tags?post=1816"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}