sp_Merge
Published: 20th July 2013
There are numerous times in which I, and many devs in the company, need to merge new data into existing tables and prior to the MERGE command this tended to be done in a procedure which created a “dev” table (table prefixed with “dev_”), executed multiple comparisons and joins, and then proceeded to perform an sp_rename to swap the dev table for the live one.

In comparisons, the MERGE statement out performed this proc by a country mile and therefore the recommendation was to move to this style of code. However, the code is quite long winded and people weren’t using it. Hence sp_merge.

Basically I developed a procedure in which you can pass 2 tables and it would merge them according to your preference (whether you want to just update records or insert / delete as well). It can also output the record counts if required so that you can see how many inserts / updates / deletes were made to your table.

I was quite pleased with the results and many devs are now using this, so I thought I’d share it with the world.

NOTE: This may well have bugs or limitations in the code, so please test prior to any deployment, but other than that, feel free to use this however you like. Just please keep my name and comments in the code.

Current known limits that I’m working on are that it doesn’t cope with identity columns… but I will find some time to add that soon.

I’ve also added an example you can play with at the bottom of this page after the code itself…

use master
go

/*********************************************************************************************
AUTHOR:        Kevin Urquhart
WEBSITE:   www.theBoredDBA.com
VERSION:   1.2

HISTORY:   1.0 - Initial Release
           1.1 - Added "silent" mode when output is not required
           1.2 - Bug fix with composite keys.  Now works.

EXAMPLE:   Source data in myStagingDatabase.dbo.mySource
           Existing table is myLiveDatabase.dbo.myLiveTable
           Primary keys is composite on pk1 and pk2
           Require INSERT, not DELETE
           Require change summary

           exec sp_merge @source = 'myStagingDatabase.dbo.mySource',
                           @mergeInto = 'myLiveDatabase.dbo.myLiveTable',
                           @pk = 'pk1, pk2',
                           @insert = 1,
                           @delete = 0,
                           @silent = 0
*********************************************************************************************/

create procedure sp_merge
(
  
@source varchar(max),
  
@mergeInto varchar(max),
  
@pk varchar(1000),
  
@insert bit = 1,
  
@delete bit = 0,
  
@silent bit = 0
)
as

set nocount on

declare
@db varchar(50), @db1 varchar(50), @schema varchar(10), @schema1 varchar(10)

if @source like '%.%.%'
begin
   select
@db = left(@source, charindex('.', @source, 1)-1)

  
select @source = replace(@source, @db + '.', '')

  
if @source like '%..%'
      
select @schema = 'dbo'
  
else
       if
@source like '%.%'
          
select @schema = left(@source, charindex('.', @source, 1)-1)
      
else
           select
@schema = 'dbo'

  
select @source = replace(@source, @schema + '.', '')
end
else
begin
   select
@db = 'tempDB'
  
  
declare @objID int, @tempCMD nvarchar(500)
  
select @tempCMD = 'use tempDB; select @objID = object_id(''' + @source + ''')'
  
exec sp_executeSQL @tempCMD, N'@objID int output', @objID output

  
select @schema = 'dbo'

  
select @tempCMD = 'select @source = name from tempDB.sys.tables where object_id = ' + convert(varchar, @objID)
  
exec sp_executeSQL @tempCMD, N'@source varchar(250) output', @source output
end

if
@mergeInto like '%.%.%'
begin
   select
@db1 = left(@mergeInto, charindex('.', @mergeInto, 1)-1)

  
select @mergeInto = replace(@mergeInto, @db1 + '.', '')

  
if @mergeInto like '%..%'
  
select @schema1 = 'dbo'
else
   if
@mergeInto like '%.%'
      
select @schema1 = left(@mergeInto, charindex('.', @mergeInto, 1)-1)
  
else
       select
@schema1 = 'dbo'

  
select @mergeInto = replace(@mergeInto, @schema1 + '.', '')
end
else
begin
   select
@db1 = 'tempDB'

  
declare @objID1 int, @tempCMD1 nvarchar(500)
  
select @tempCMD1 = 'use tempDB; select @objID = object_id(''' + @mergeInto + ''')'
  
exec sp_executeSQL @tempCMD1, N'@objID int output', @objID1 output

  
select @schema1 = 'dbo'
  
select @tempCMD1 = 'select @source = name from tempDB.sys.tables where object_id = ' + convert(varchar, @objID1)
  
exec sp_executeSQL @tempCMD1, N'@source varchar(250) output', @mergeInto output
end

declare
@sql nvarchar(max)

declare @compare varchar(max), @keys varchar(max), @set varchar(max), @insertList varchar(max)

select @pk = replace(@pk, ', ', ''', ''')

select @sql = 'select @compare = isnull(@compare + '' '', '''') + ''(select x.'' + a.name + '' except select z.'' + a.name + '') is not null or'',
@set = isnull(@set + '' '', '''') + ''x.'' + a.name + '' = z.'' + a.name + '', '',
@insert = isnull(@insert + '' '', '''') + ''z.'' + a.name + '', ''
from
(
select c.name
from '
+ @db + '.sys.columns c
join '
+ @db + '.sys.objects o
on c.object_id = o.object_id
where o.name = '''
+ @source + '''
and c.is_computed = 0
and c.name not in ('''
+ @pk + ''')
) a
join
(
select c.name
from '
+ @db1 + '.sys.columns c
join '
+ @db1 + '.sys.objects o
on c.object_id = o.object_id
where o.name = '''
+ @mergeInto + '''
and c.is_computed = 0
and c.name not in ('''
+ @pk + ''')
) b
on a.name = b.name'

exec sp_executeSQL @sql, N'@compare varchar(max) output, @set varchar(max) output, @insert varchar(max) output',
      
@compare output, @set output, @insertList output

select @sql = 'select @keys = isnull(@keys + '' '', '''') + ''x.'' + c.name + '' = z.'' + c.name + '' and''
from '
+ @db + '.sys.columns c
join '
+ @db + '.sys.objects o
on c.object_id = o.object_id
where o.name = '''
+ @source + '''
and c.name in ('''
+ @pk + ''')'

exec sp_executeSQL @sql, N'@keys varchar(max) output', @keys output

declare @mergeCommand varchar(max)

select @mergeCommand = 'merge ' + @db1 + '.' + @schema1 + '.' + @mergeInto + ' x
using '
+ @db + '.' + @schema + '.' + @source + ' z on
(
'
+ left(@keys, len(@keys)-3) + '
)
when matched and
(
'
+ left(@compare, len(@compare) - 3) + '
) then
update
set
'
+ left(@set, len(@set) - 1) + ''

if @insert = 1
  
select @mergeCommand += '
when not matched then
insert('
+ replace(@pk, ''', ''', ', ') + ', ' + replace(left(@insertList, len(@insertList)-1), 'z.', '') + ')
values(z.'
+ replace(@pk, ''', ''', ', z.') + ', ' + left(@insertList, len(@insertList)-1) + ')'

if @delete = 1
  
select @mergeCommand += '
when not matched by source then
delete'

if @silent = 0
begin
   select
@mergeCommand += '
output $action;'

  
declare @output table
  
(
      
actionType varchar(20)
   )
  
insert into @output
  
exec (@mergeCommand)
  
--print @mergeCommand

  
declare @noInsert int, @noDelete int, @update int

   select
@noInsert = count(*) from @output where actionType = 'INSERT'
  
select @noDelete = count(*) from @output where actionType = 'DELETE'
  
select @update = count(*) from @output where actionType = 'UPDATE'

  
print 'Merged into: ' + @db1 + '.' + @schema1 + '.' + @mergeInto
  
print 'No. of Records Inserted: ' + convert(varchar, @noInsert)
  
print 'No. of Records Deleted: ' + convert(varchar, @noDelete)
  
print 'No. of Records Updated: ' + convert(varchar, @update)
end
else
begin
   select
@mergeCommand += ';'

  
exec (@mergeCommand)
  
--print @mergeCommand
end

set nocount off
span>


Example to play with:

if object_id('mySource') is not null drop table mySource
if object_id('myLive') is not null drop table myLive
go

create table mySource
(
  
id int,
  
fullName varchar(50)
)
insert into mySource values(1, 'Homer Simpson'), (2, 'Marge Simpson'), (4, 'Maggie Simpson')

create table myLive
(
  
id int,
  
fullName varchar(50)
)
insert into myLive values(1, 'Bart Simpson'), (2, 'Marge Simpson'), (3, 'Lisa Simpson')
go

exec sp_merge 'master.dbo.mySource', 'master.dbo.myLive', 'id', 0, 0, 0
--exec sp_merge 'master.dbo.mySource', 'master.dbo.myLive', 'id', 1, 0, 0
--exec sp_merge 'master.dbo.mySource', 'master.dbo.myLive', 'id', 0, 1, 0
--exec sp_merge 'master.dbo.mySource', 'master.dbo.myLive', 'id', 1, 1, 0
--exec sp_merge 'master.dbo.mySource', 'master.dbo.myLive', 'id', 1, 1, 1
go

select *
from myLive
Comments:
NB: Comments will only appear once they have been moderated.