Copy changed data in table from one database to another in SQL stored procedure

I was working on scenario where I have to synchronize the destination database table as per the changed or newly added data in the source database. Both database were having identical schema but need was my table names are not fixed and I want that I will provide only list of tables to some stored procedure and that procedure will dynamically add or update data of each table. So for this I had implemented a dynamic query which uses “Merge” statement to insert, update modified or added records in target table from source table.

I had created one stored procedure which takes source database and destination database name as parameter and accordingly creates dynamic Merge statement for each table of that database. Delete operation was required in my case so I haven’t included but you can easily add it.

Dynamic SQL statements like below gets created for each table.

BEGIN TRAN;
SET IDENTITY_INSERT [destdb].[dbo].[YourTableName]  on
MERGE [destdb].[dbo].[YourTableName] AS  T
USING [sourcedb].[dbo].[YourTableName] AS  S
ON (T.PrimaryKeyColumnName = S.PrimaryKeyColumnName) 
WHEN NOT MATCHED BY TARGET 
THEN  INSERT(MyId,MyValue,ModifiedDate) VALUES(S.MyId, S.MyValue,S.ModifiedDate) WHEN MATCHED AND S.ModifiedDate > T.ModifiedDate
THEN UPDATE SET T.MyValue=S.MyValue,T.ModifiedDate = S.ModifiedDate;	
OUTPUT $action, inserted.*, deleted.*;				
SET IDENTITY_INSERT [destdb].[dbo].[YourTableName]  OFF
ROLLBACK TRAN;

In above code I have used Merge statement to handle insert update, though transaction is not required here still for better understanding I have included it and shown output of inserted and deleted table.
In actual code you can remove transaction related code or can simply do “commit” rather “rollback” and remove that output $action code.

To know more about merge statement in SQL, visit here.

In my case I was having column named “ModifiedDate” in each table so I had added condition of source.ModifiedDate > target.Modified data to avoid unchanged records but if you are not having it then you can remove it.

Merge statement works on “Primary Key” column to compare the records of source and target table so in stored procedure I have dynamically found primary key column and then for rest other columns I have built a separate string and later concatenated all strings together to form a merge statement.
 

Below is the stored procedure code.

IF Object_id('[dbo].[SyncTables]', 'P') IS NOT NULL
	DROP PROCEDURE [dbo].[SyncTables]
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO 

CREATE PROCEDURE [dbo].[SyncTables]
(
	 @SourceDBName NVARCHAR(128),
	 @DestDBName NVARCHAR(128) 
)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @TableName NVARCHAR(128)

	IF OBJECT_ID('tempdb..#tmpTbls') IS NOT NULL
		DROP TABLE #tmpTbls

	CREATE TABLE #tmpTbls (TableName VARCHAR(max))

	INSERT #tmpTbls
	SELECT NAME AS TableName
		FROM sys.tables
		WHERE type_desc = 'USER_TABLE'
			AND [type] = 'U'

	DECLARE curTableList CURSOR
	FOR
	SELECT TableName
	FROM #tmpTbls

	OPEN curTableList

	FETCH NEXT
	FROM curTableList
	INTO @TableName

	WHILE @@fetch_status = 0
	BEGIN
		 
			
			DECLARE @PrimaryKeyColumn NVARCHAR(128)
			DECLARE @qry NVARCHAR(max)
			DECLARE @justColumnNamesLine VARCHAR(max) = ''  
			DECLARE @insertColumnsLine VARCHAR(max) = '' 
			DECLARE @updateColumnsLine VARCHAR(max) = ''  
			DECLARE @CurrentColumn NVARCHAR(128) = NULL

			CREATE TABLE #tmpColumns (column_name NVARCHAR(128))

			SET @qry = 'insert into #tmpColumns SELECT  col.column_name FROM 
						information_schema.table_constraints tc
						INNER JOIN information_schema.key_column_usage col
						 ON col.Constraint_Name = tc.Constraint_Name 
						 AND col.Constraint_schema = tc.Constraint_schema 
						 WHERE tc.Constraint_Type = ''Primary Key'' AND col.Table_name = ''' + @TableName + ''''
 
			EXECUTE sp_executesql @qry

			SELECT @PrimaryKeyColumn = column_name
			FROM #tmpColumns

			TRUNCATE TABLE #tmpColumns

			SET @qry = ''
			SET @qry = 'insert into #tmpColumns select column_name from information_schema.columns where table_name = ''' + @TableName + '''and Column_name <> ''' + @PrimaryKeyColumn + ''' and column_name <> ''ModifiedDate'''

			EXECUTE sp_executesql @qry

			SET @CurrentColumn = (
					SELECT TOP 1 column_name
					FROM #tmpColumns
					)
			SET @qry = ''
			SET @qry = 'BEGIN TRAN;
						SET IDENTITY_INSERT [' + @DestDBName + '].[dbo].[' + @TableName + ']  on
						MERGE [' + @DestDBName + '].[dbo].[' + @TableName + '] AS  T
						USING ['+@SourceDBName+'].[dbo].[' + @TableName + '] AS  S
						ON (T.' + @PrimaryKeyColumn + ' = S.' + @PrimaryKeyColumn + ') 
						WHEN NOT MATCHED BY TARGET 
						THEN  INSERT(' + @PrimaryKeyColumn + ','

			WHILE isnull(@CurrentColumn, '') <> ''
			BEGIN
				SET @justColumnNamesLine = @justColumnNamesLine + @CurrentColumn + ','
				SET @insertColumnsLine = @insertColumnsLine + + 'S.' + @CurrentColumn + ','
				SET @updateColumnsLine = @updateColumnsLine + 'T.' + @CurrentColumn + '=S.' + @CurrentColumn + ','

				--print @CurrentColumn 
				DELETE
				FROM #tmpColumns
				WHERE column_name = @CurrentColumn

				SET @CurrentColumn = (
						SELECT TOP 1 column_name
						FROM #tmpColumns
						)
			END

			SET @justColumnNamesLine = SUBSTRING(@justColumnNamesLine, 0, LEN(@justColumnNamesLine))
			SET @insertColumnsLine = SUBSTRING(@insertColumnsLine, 0, LEN(@insertColumnsLine))
			SET @updateColumnsLine = SUBSTRING(@updateColumnsLine, 0, LEN(@updateColumnsLine))
			SET @qry = @qry + @justColumnNamesLine + ',ModifiedDate) VALUES(S.' + @PrimaryKeyColumn + ', ' + @insertColumnsLine + ',S.ModifiedDate) WHEN MATCHED AND S.ModifiedDate > T.ModifiedDate
						THEN UPDATE SET ' + @updateColumnsLine + ',T.ModifiedDate = S.ModifiedDate;							 		
						SET IDENTITY_INSERT [' + @DestDBName + '].[dbo].[' + @TableName + ']  OFF
						COMMIT TRAN;'
						 

			--PRINT @qry
			--PRINT '=========================='
			EXEC sp_executesql @qry
			
			
			
			DROP TABLE #tmpColumns
			 
		FETCH NEXT
		FROM curTableList
		INTO @TableName
	END

	CLOSE curTableList

	DEALLOCATE curTableList

	SET NOCOUNT OFF
END

In above code I have taken all the tables from sys.tables but in actual I had created another stored procedure which provides table list as per different parameters. So you can alter that code as per your requirement.

Hope you have benefited from this post. There might some another and better ways to handle this kind of scenario so if you know it share it in comment section below. Thanks.