posts - 219, comments - 411, trackbacks - 27

My Links

Advertisement

News

Follow billgraziano on Twitter

Article Categories

Archives

Post Categories

Consulting

SQL Server

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 dba_GenerateMergeStatement
(@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 IDENTITY_INSERT [dbo].[' + @table + '] ON;
'



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

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


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


SELECT @list = @list + 's.' + [name] +', '
from sys.columns
where object_id = object_id(@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.COLUMN_NAME + '] = s.[' + c.COLUMN_NAME + '] AND '
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @table
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

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 = object_id(@table)
-- don't update primary keys
and [name] NOT IN (SELECT [column_name]
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @table
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME)
-- 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 object_id = object_id(@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 object_id = object_id(@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(object_id(@table), 'TableHasIdentity')
if @return = 1
PRINT 'SET IDENTITY_INSERT [dbo].[' + @table + '] OFF;
'

PRINT ''
PRINT 'GO'
PRINT '';

Print | posted on Tuesday, February 15, 2011 7:47 AM | Filed Under [ SQL Server Stuff Utilities ]

Feedback

Gravatar

# re: Generate MERGE statements from a table

This is a great idea, as developers we should automate our work as much as possible, it is not only quicker, but more reliable too.
I have created a set-of tools for SQL Server which automates a lot of the things I do, such as building standard i/o procedures, but I had not thought of this situation.
The merge statement was new to SQL 2008 and is not something I have tried yet, but your post has made me aware of it and when it might be useful. I am also responsible for some large data warehouse systems, and I can see that this could be useful in loading those tables daily too.
Thanks
3/8/2011 4:14 AM | Andrew Mogford
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET