Generate MERGE statements from a table

We have a requirement to build a test environment where certain tables get reset from production every night.  These are mainly lookup tables.  I played around with all kinds of fancy solutions and finally settled on a series of MERGE statements.  And being lazy I didn’t want to write them myself. 

The stored procedure below will generate a MERGE statement for the table you pass it.  If you have identity values it populates those properly.  You need to have primary keys on the table for the joins to be generated properly.  The only thing hard coded is the source database.  You’ll need to update that for your environment.  We actually used a linked server in our situation.

CREATE PROC dbaGenerateMergeStatement
(@table NVARCHAR(128) )
AS
set nocount on;
declare @return int;


PRINT '-- ' + @table + ' -------------------------------------------------------------'
--PRINT 'SET NOCOUNT ON;
--'

-- Set the identity insert on for tables with identities
select @return = objectproperty(object
id(@table), 'TableHasIdentity')
if @return = 1
PRINT 'SET IDENTITYINSERT [dbo].[' + @table + '] ON;
'



declare @sql varchar(max) = ''
declare @list varchar(max) = '';

SELECT @list = @list + [name] +', '
from sys.columns
where object
id = objectid(@table)


SELECT @list = @list + [name] +', '
from sys.columns
where object
id = objectid(@table)


SELECT @list = @list + 's.' + [name] +', '
from sys.columns
where object
id = objectid(@table)

-- --------------------------------------------------------------------------------
PRINT 'MERGE [dbo].[' + @table + '] AS t'
PRINT 'USING (SELECT * FROM [source
database].[dbo].[' + @table + ']) as s'

-- Get the join columns ----------------------------------------------------------
SET @list = ''
select @list = @list + 't.[' + c.COLUMNNAME + '] = s.[' + c.COLUMNNAME + '] AND '
from INFORMATIONSCHEMA.TABLECONSTRAINTS pk ,
INFORMATIONSCHEMA.KEYCOLUMNUSAGE c
where pk.TABLE
NAME = @table
and CONSTRAINTTYPE = 'PRIMARY KEY'
and c.TABLE
NAME = pk.TABLENAME
and c.CONSTRAINT
NAME = pk.CONSTRAINTNAME

SELECT @list = LEFT(@list, LEN(@list) -3)
PRINT 'ON ( ' + @list + ')'


-- WHEN MATCHED ------------------------------------------------------------------
PRINT 'WHEN MATCHED THEN UPDATE SET'

SELECT @list = '';
SELECT @list = @list + ' [' + [name] + '] = s.[' + [name] +'],
'

from sys.columns
where object
id = objectid(@table)
-- don't update primary keys
and [name] NOT IN (SELECT [column
name]
from INFORMATIONSCHEMA.TABLECONSTRAINTS pk ,
INFORMATIONSCHEMA.KEYCOLUMNUSAGE c
where pk.TABLE
NAME = @table
and CONSTRAINTTYPE = 'PRIMARY KEY'
and c.TABLE
NAME = pk.TABLENAME
and c.CONSTRAINT
NAME = pk.CONSTRAINTNAME)
-- and don't update identity columns
and columnproperty(object
id(@table), [name], 'IsIdentity ') = 0
--print @list
PRINT left(@list, len(@list) -3 )

-- WHEN NOT MATCHED BY TARGET ------------------------------------------------
PRINT ' WHEN NOT MATCHED BY TARGET THEN';

-- Get the insert list
SET @list = ''

SELECT @list = @list + '[' + [name] +'], '
from sys.columns
where objectid = objectid(@table)

SELECT @list = LEFT(@list, LEN(@list) - 1)

PRINT ' INSERT(' + @list + ')'

-- get the values list
SET @list = ''

SELECT @list = @list + 's.[' +[name] +'], '
from sys.columns
where objectid = objectid(@table)

SELECT @list = LEFT(@list, LEN(@list) - 1)

PRINT ' VALUES(' + @list + ')'

-- WHEN NOT MATCHED BY SOURCE
print 'WHEN NOT MATCHED BY SOURCE THEN DELETE; '

PRINT ''

PRINT 'PRINT ''' + @table + ': '' + CAST(@@ROWCOUNT AS VARCHAR(100));';
PRINT ''

-- Set the identity insert OFF for tables with identities
select @return = objectproperty(objectid(@table), 'TableHasIdentity')
if @return = 1
PRINT 'SET IDENTITY
INSERT [dbo].[' + @table + '] OFF;
'

PRINT ''
PRINT 'GO'
PRINT '';