- Posted On 10 July 2014
- In Programming
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.
Top 10 Visual Studio things which can boost developers coding speed
Visual Studio 2012 provides some coding features by which you can code faster if use them properly. This post will cover top 10 things among them to boost your development speed.
How to call click or any event only once in jQuery
Know how to execute an click event or any event only once for any element in jQuery. Perform action only once and even not required to unbind event.
Speed up coding in Visual Studio with code snippets & samples at your fingertips
Know how you can speed up coding in Visual Studio with Bing Developer Assistant by having millions of code snippets and sample projects at fingertips.
Assembla - Free and private repository to manage your source code online with SVN subversion hosting
With Assembla you can share source code with others online. Free & Private source code repository with SVN Subversion, Git & Perforce Hosting.
Visual Studio 2008 Shell and TFS integration
Visual Studio 2008 Shell and TFS integration is the problem for all newbies of BIDS and TFS. Here is the solution.