there's a neat trick using FOR XML to get a delimited list of columns.
here's a snippet i wrote that i use in a procedure to generate sample insert/update/merge statements.
it assumes i'm working with a similarly named table, like Invoice/Stage_Invoice, but it's a solid model for you to tweak:
DECLARE @Tablename SYSNAME = '[dbo].[CommandLog]'
SELECT DISTINCT UColumns,
t.name,
InsertCommand =
'INSERT INTO ' + quotename( t.name) + '(' + sq.IColumns + ')' + CHAR(13) + CHAR(10)
+ ' SELECT ' + REPLACE(sq.IColumns,'[','MySource.[') + CHAR(13) + CHAR(10)
+ ' FROM [Stage' +t.name + '] MySource' + CHAR(13) + CHAR(10)
+ ' LEFT OUTER JOIN ' + quotename( t.name) + 'MyTarget ' + CHAR(13) + CHAR(10)
+ ' ON MySource.[PK] = MyTarget.[PK] ' + CHAR(13) + CHAR(10)
+ ' AND MySource.[Col2] = MyTarget.[Col2]' + CHAR(13) + CHAR(10)
+ ' WHERE MyTarget.[PK] IS NULL',
UpdateCommand =
'UPDATE MyTarget ' + CHAR(13) + CHAR(10)
+ 'SET ' + sq.Columns + ' ' + CHAR(13) + CHAR(10)
+ '--SELECT * ' + CHAR(13) + CHAR(10)
+ 'FROM [Staging' + t.name + '] MySource ' + CHAR(13) + CHAR(10)
+ 'INNER JOIN ' + quotename(t.name) + ' MyTarget ' + CHAR(13) + CHAR(10)
+ 'ON MySource.[PK] = MyTarget.[PK] ' + CHAR(13) + CHAR(10)
+ 'AND MySource.[Col2] = MyTarget.[Col2]',
MergeCommand =
'MERGE INTO '
+ quotename(object_schema_name(t.OBJECT_ID))
+ '.'
+ quotename(t.name)
+ ' [MyTarget]'
+ 'USING ' + CHAR(13) + CHAR(10)
+ '(' + CHAR(13) + CHAR(10)
+ 'SELECT ' + CHAR(13) + CHAR(10)
+ REPLACE(sq.IColumns,'[','stg.[') + CHAR(13) + CHAR(10)
+ ' FROM [Staging' + t.name + '] stg ' + CHAR(13) + CHAR(10)
+') AS [MySource]' + CHAR(13) + CHAR(10)
+ ' ON MySource.[PK] = MyTarget.[PK]' + CHAR(13) + CHAR(10)
+' WHEN NOT MATCHED ' + CHAR(13) + CHAR(10)
+' THEN INSERT ' + CHAR(13) + CHAR(10)
+'(' + CHAR(13) + CHAR(10)
+ sq.IColumns + CHAR(13) + CHAR(10)
+')' + CHAR(13) + CHAR(10)
+' VALUES ' + CHAR(13) + CHAR(10)
+'(' + CHAR(13) + CHAR(10)
+ REPLACE(sq.IColumns,'[','MySource.[') + CHAR(13) + CHAR(10)
+' )' + CHAR(13) + CHAR(10)
+'WHEN MATCHED ' + CHAR(13) + CHAR(10)
+'THEN UPDATE ' + CHAR(13) + CHAR(10)
+'SET ' + CHAR(13) + CHAR(10)
+ UColumns + CHAR(13) + CHAR(10)
FROM sys.objects t
JOIN (SELECT
OBJECT_ID,
Columns = STUFF((SELECT
',' + CHAR(13) + CHAR(10) + ' MyTarget.' + QUOTENAME(sc.name) + SPACE(30 - LEN(sc.name)) + ' = MySource.' + QUOTENAME(sc.name)
FROM sys.columns sc
WHERE sc.object_id = s.object_id
ORDER BY sc.column_id
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,''),
IColumns = STUFF((SELECT
',' + QUOTENAME(si.name)
FROM sys.columns si
WHERE si.object_id = s.object_id
ORDER BY si.column_id
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,''),
UColumns = STUFF((SELECT
',' + 'MyTarget.'+ QUOTENAME(su.name) +' = MySource.'+ QUOTENAME(su.name)
FROM sys.columns su
WHERE su.object_id = s.object_id
ORDER BY su.column_id
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
FROM sys.columns s) sq
ON t.object_id = sq.object_id
WHERE t.object_id = object_id(@Tablename)
Lowell