Skip to main content

Merge data in sql server without merge statement (supports major version of sql server)

This article is basically for whom who cannot use merge statement of sql server (because they are still stuck with the old sql server which no longer supports merge statement) or who are aware of the performance of the merge statement.

Recently I got to know that a client I am working for was still using sql server 2005 and not in mood to switch to new version so some alternative of merge statement must have been found, so I searched a lot and got some consolidated solution and made a query structure just like same as merge statement.

Suppose we have a table named Instance and data that we need to merge with respect to Id that is identity column

(1, 'Default'  ,1,0,1,GetDate(),NULL,NULL ),           
(2, 'MyInstance' ,1,0,1,GetDate(),NULL,NULL ) 
We need to do following steps to merge data if you are familiar with the Merge statement structure or pattern then it's easy to understand for you rest of the people have to pay just a little bit more attention.

The following steps include in the query -

1) Clone the structure of the source table into temporary table named #Source
2) If table has identity column then set Identity insert On
3) Insert destination data into #Source table
4) If you are followed the step 2 then set Identity Insert Off
5) Update source table (i.e [Instance] table) with the matching condition data of the #Source table (just like below example #Source join [Instances] on #Source.Id = [Instances].Id).
6) Delete the matching record in #Source table.
7) Insert the rest of the data of #Source table into source table (i.e. Instance table).
8) one more step that which is not the part of this article is to delete not matching record from the source table, this should be the 5th step and every later step must be shifted by the one position :-).


Merge Data with Identity Comparison -

SELECT * INTO #Source FROM [Instances] WHERE Id <> Id

SET IDENTITY_INSERT #Source ON

insert  into #Source ([Id]         
                     ,[InstanceDescription]
                     ,[IsActive]
                     ,[IsDeleted]
                     ,[CreatedBy]
                     ,[CreatedOn]
                     ,[LastModifiedBy]
                     ,[LastModifiedOn]
                     )
   VALUES          
       (1, 'Default' ,1,0,1,GetDate(),NULL,NULL ),           
       (2, 'MyInstance'       ,1,0,1,GetDate(),NULL,NULL )     

 SET IDENTITY_INSERT #Source Off

update [Instances] set
       [InstanceDescription]      = #Source.[InstanceDescription],
       [IsActive]                 = #Source.[IsActive],     
       [IsDeleted]                = #Source.[IsDeleted],    
       [LastModifiedBy]           = #Source.[LastModifiedBy],
       [LastModifiedOn]           = GetDate()
from #Source join [Instances] on #Source.Id = [Instances].Id

delete #Source from #Source join [Instances] on #Source.Id = [Instances].Id 

SET IDENTITY_INSERT [Instances] ON
insert into [Instances]
              (
              [Id]
              ,[InstanceDescription]       
              ,[IsActive]     
              ,[IsDeleted]    
              ,[CreatedBy]    
              ,[CreatedOn]    
              ,[LastModifiedBy]
              ,[LastModifiedOn]
              )
select        [Id]
              ,[InstanceDescription]       
              ,[IsActive]     
              ,[IsDeleted]    
              ,[CreatedBy]    
              ,[CreatedOn]    
              ,[LastModifiedBy]
              ,[LastModifiedOn]
from #Source
SET IDENTITY_INSERT [Instances] OFF

DROP TABLE #Source

Go

Merge Data using another two or more column instead of identity column -

Suppose you have a table named ModuleSetting and want to merge data with respect to ModuleId and Instance Id instead of Id (i.e. identity column) because this is not the transaction table not the master table and you don’t know what would be the identity.
All the steps are as per above steps except to found matching record that is join query, you only need to make a change in join with your matching criteria that's all, go through the below query.

Merge Data with Two or more column comparison -

SELECT [ModuleId]
       ,[MenuSequence]
       ,[SubMenuSequence]
       ,[GlobalSearchSequence]
       ,[InstanceId]
       ,[IsDeleted]
       ,[CreatedBy]
       ,[CreatedOn]
       ,[LastModifiedBy]
       ,[LastModifiedOn] INTO #Source FROM [ModuleSettings] WHERE Id = 0

insert  into #Source ([ModuleId]
                      ,[MenuSequence]
                      ,[SubMenuSequence]
                      ,[GlobalSearchSequence]
                      ,[InstanceId]
                      ,[IsDeleted]
                      ,[CreatedBy]
                      ,[CreatedOn]
                      ,[LastModifiedBy]
                      ,[LastModifiedOn]
                     ) 
   VALUES    
(1, 1 ,       0,     1      ,NULL  ,0,1,GetDate(),NULL,NULL ),      
       (2, 2 ,       0,     2      ,NULL  ,0,1,GetDate(),NULL,NULL ),      
       (3, 3 ,       0,     3      ,NULL  ,0,1,GetDate(),NULL,NULL ),

update [ModuleSettings] set
       [ModuleId]                 = #Source.[ModuleId],     
       [MenuSequence]              = #Source.[MenuSequence],     
       [SubMenuSequence]          = #Source.[SubMenuSequence],     
       [GlobalSearchSequence]     = #Source.[GlobalSearchSequence],     
       [InstanceId]               = #Source.[InstanceId],     
       [IsDeleted]                = #Source.[IsDeleted],    
       [LastModifiedBy]           = #Source.[LastModifiedBy],
       [LastModifiedOn]           = GetDate()
from #Source join [ModuleSettings]
ON [ModuleSettings].[ModuleId] = #Source.[ModuleId] and [ModuleSettings].[InstanceId] is null

delete #Source from #Source join [ModuleSettings] ON [ModuleSettings].[ModuleId] = #Source.[ModuleId] and [ModuleSettings].[InstanceId] is null


insert into [ModuleSettings]
              (
               [ModuleId]
              ,[MenuSequence]
              ,[SubMenuSequence]
              ,[GlobalSearchSequence]
              ,[InstanceId]
              ,[IsDeleted]
              ,[CreatedBy]
              ,[CreatedOn]
              ,[LastModifiedBy]
              ,[LastModifiedOn]
              )
select
               [ModuleId]
              ,[MenuSequence]
              ,[SubMenuSequence]
              ,[GlobalSearchSequence]
              ,[InstanceId]
              ,[IsDeleted]
              ,[CreatedBy]
              ,[CreatedOn]
              ,[LastModifiedBy]
              ,[LastModifiedOn]
from #Source

DROP TABLE #Source
Go



Popular posts from this blog

Regular expression for alphanumeric with space in asp.net c#

How to validate that string contains only alphanumeric value with some spacial character and with whitespace and how to validate that user can only input alphanumeric with given special character or space in a textbox (like name fields or remarks fields). In remarks fields we don't want that user can enter anything, user can only able to enter alphanumeric with white space and some spacial character like -,. etc if you allow. Some of regular expression given below for validating alphanumeric value only, alphanumeric with whitspace only and alphanumeric with whitespace and some special characters.

Uploading large file in chunks in Asp.net Mvc c# from Javascript ajax

Often we have a requirement to upload files in Asp.net, Mvc c# application but when it comes to uploading larger file, we always think how to do it as uploading large file in one go have many challenges like UI responsiveness, If network fluctuate for a moment in between then uploading task get breaks and user have to upload it again etc.

How to validate dropdownlist in JavaScript

In this article you will see how to put validation in dropdownlist by javascript, suppose first item value of dropdownlist is 0 and text is "-Select-" just like given below and we have to validate that at least one item is selected excluding default i.e "-Select-".

regex - check if a string contains only alphabets c#

How to validate that input string contains only alphabets, validating that textbox contains only alphabets (letter), so here is some of the ways for doing such task. char have a property named isLetter which is for checking if character is a letter or not, or you can check by the regular expression  or you can validate your textbox through regular expression validator in asp.net. Following code demonstrating the various ways of implementation.