Use SQL To Write Your SQL Code!

Let’s face it, programmers hate to code anything manually and with tools like SQL Server why should they. 
I recently had to code Merge statements for thirty tables so I decided to use the power of SQL to help me out.  Since
SQL Server already has all the column names for the tables in its catalog tables, I decided to leverage that.   The
idea of using SQL scripts to automatically generate SQL code is one that can be applied to many situations, especially when you need to process many different database objects.   This code includes a feature to omit the comma on the last column.   

This solution makes use of a design pattern we use at my company in which we pull new data into a staging table first and then use a Merge statement to get that data added to the accumulated history of data.  You may need to make some modifications to fit your needs.  Note:  In our case, primary keys are always the tablename_ID which I make use of in this script.

In the code below, replace @tablename with the name of your source table and @source_schema with the schema of your source table.

I hope you find this blog entry useful and appreciate any feedback. 

The Code

set nocount on 

DECLARE @tablename VARCHAR(100)
DECLARE @source_schema VARCHAR(50)

select @tablename = 'hotel'
SELECT @source_schema = 'stg'

--SELECT * FROM sys.schemas AS ss WHERE ss.name = @source_schema

print '-- Author: Bryan Caferky, BI Team      Date: ' + cast(GetDate() as varchar(12))                                   
print '--     '
print 'MERGE INTO ts.' + @tablename + ' AS target '
print '     USING'
print '( select   '

SELECT '                  ' + sc.name + case sc.column_id when (SELECT  max(sc.column_id) FROM sys.columns sc JOIN sys.tables st ON (sc.object_id = st.object_id) WHERE st.name = @tablename AND st.schema_id = 5 )  then ' ' else ', ' end
FROM sys.columns sc 
JOIN sys.tables st ON (sc.object_id = st.object_id) 
JOIN sys.schemas ss ON (st.schema_id = ss.schema_id)
WHERE st.name = @tablename AND  ss.name = @source_schema
ORDER BY sc.column_id

print '  from stg.' + @tablename  
print '        ) AS source'
print '    ON'
print '    target.' + @tablename + '_id = source.' + @tablename + '_id'

print '    WHEN MATCHED '
print '        THEN UPDATE'
print '          SET '     
-- Merge Update columns...
SELECT  '                   ' + 'target.' + sc.name + ' = source.' + sc.name +  case sc.column_id when (SELECT  max(sc.column_id) FROM sys.columns sc JOIN sys.tables st ON (sc.object_id = st.object_id) WHERE st.name = @tablename AND st.schema_id = 5 )  then ' ' else ', ' end
FROM sys.columns sc
JOIN sys.tables st ON (sc.object_id = st.object_id) 
JOIN sys.schemas ss ON (st.schema_id = ss.schema_id)
WHERE st.name = @tablename AND  ss.name = @source_schema
ORDER BY sc.column_id
-- Merge Insert columns...
print  '    WHEN NOT MATCHED'
print  '     THEN INSERT ('

-- Do not put a comma on the last line...
SELECT  '                   ' + sc.name + case sc.column_id when (SELECT  max(sc.column_id) FROM sys.columns sc JOIN sys.tables st ON (sc.object_id = st.object_id) WHERE st.name = @tablename AND st.schema_id = 5 )  then ' ' else ', ' end
FROM sys.columns sc
JOIN sys.tables st ON (sc.object_id = st.object_id) 
JOIN sys.schemas ss ON (st.schema_id = ss.schema_id)
WHERE st.name = @tablename AND  ss.name = @source_schema
ORDER BY sc.column_id

print '          )'
print '       VALUES ('

SELECT  '                   ' + sc.name + case sc.column_id when (SELECT  max(sc.column_id) FROM sys.columns sc JOIN sys.tables st ON (sc.object_id = st.object_id) WHERE st.name = @tablename AND st.schema_id = 5 )  then ' ' else ', ' end
FROM sys.columns sc
JOIN sys.tables st ON (sc.object_id = st.object_id) 
JOIN sys.schemas ss ON (st.schema_id = ss.schema_id)
WHERE st.name = @tablename AND  ss.name = @source_schema
ORDER BY sc.column_id

print '          )'
print 'OUTPUT'
print '     $ACTION;'

SET NOCOUNT OFF

Before you can run this script in SQL Server Management Studio (SSMS), you need to make some changes to the way output is formatted.  First, the Query drop down menu and select Query Options.  Unselect ‘Include column headers in the result set’ and increase the ‘Maximum number of character displayed in each column’ to 500 just so we know there is enough room for the statement lines.

Finally, you need to change the default output results format to Text as shown below. 

Now you’re good to go. 

Again, the use of SQL and SSMS to generate code for you is a powerful way to increase your productivity so consider adding this approach to your toolkit.

Bryan