Extract table data into a merge statement with unique column

/*

This script extracts the existing data in a table
and places it into a merge statement to allow the 
table to be populated with the same data.

select * from dbo.MyTable


*/

-- specify the name of the table whose data is to be output
:setvar TableName dbo.MyTable

-- specify the column name that uniquely identifies the row (usually an identity column)
:setvar UniqueCol RowID

-- declare and set a variable so we'll know if the identity column data, if any, should be output
:setvar IncludeIdentity 1




-- make sure the temp table does not already exist
IF (OBJECT_ID('tempdb..#TempData') IS NOT NULL)
begin
DROP TABLE #TempData
end
GO

-- create a temp table 
CREATE TABLE #TempData
(
lColOrder int not null primary key
, sName varchar(255) not null
, bIsIdentity bit not null default(0)
, DataTypeId int not null
, bUseTicks bit not null default(0)
, ColData varchar(max) null	--not null default('null')

, unique (sName, lColOrder)
)
GO

-- turn on nocount.
set nocount on;

-- insert the table columns into our temp table.
INSERT into #TempData
(lColOrder, sName, bIsIdentity, DataTypeId )
select column_id, [name], a.is_identity, system_type_id
from sys.columns a
where a.[object_id] = object_id('$(TableName)')
and a.is_computed = 0
--and (a.is_identity = 0 or $(IncludeIdentity) = 1)
and a.system_type_id = 189	-- exclude rowversion columns.
--
--and a.column_id 
-- get the smallest ColOrder value
declare @lFirstColOrder int
set @lFirstColOrder = (
select top 1 lColOrder from #TempData order by lColOrder
)

-- update the 'Use Ticks' column for the data types that need to be enclosed in single quote characters.
UPDATE #TempData set
bUseTicks = 1
where DataTypeId in (167, 35, 175, 40, 41, 61, 36)

-- declare a variable to use for our execute command statements.
declare @cmd varchar(max)

-- declare some variables we'll use a little later.
declare @sCurrentCol varchar(255)
, @lDataType int
, @RowID int
, @lSrcRowCount int = 0

-- update the [ColData] column to be null for all rows.
UPDATE #TempData set ColData=null


-- declare a variable to hold the output line.
declare @sOutputLine varchar(max)
declare @crlf varchar(2) = char(13) + char(10)
, @tab varchar(1) = char(9)


print '/*'
print ''
print '	delete from $(TableName)'
print ''
print '*/'


-- turn on the identity insert.
print 'set identity_insert $(TableName) on' + @crlf + @crlf

-- start the CTE portion of the output
set @sOutputLine = ';with cteSrc as (
SELECT *
from ( values' + @crlf
print @sOutputLine



-- declare & open the columns cursor.
DECLARE curColumns cursor scroll read_only for
select a.sName, a.DataTypeID 
from #TempData a 
order by a.lColOrder
OPEN curColumns

---- declare & open the source table cursor
--DECLARE curSourceData cursor fast_forward for
--	select $(UniqueCol) 
--	from $(TableName)
--	order by $(UniqueCol) 
--OPEN curSourceData

---- get the next row of data from the source table cursor.
--FETCH next from curSourceData into @RowID

DECLARE @lSourceRowCount int;
SELECT @lSourceRowCount = count(*)
from $(TableName)

declare @lCurrentSourceRow int = 1;

-- keep going while we're still pointing to a valid row number
WHILE (@lCurrentSourceRow)
begin
-- get the 1st row from the columns cursor.
FETCH first from curColumns into @sCurrentCol, @lDataType

-- increment our row count
set @lSrcRowCount = @lSrcRowCount + 1



-- keep going while the last fetch was good.
WHILE (@@fetch_status = 0)
begin
-- build the command statement for the current column
set @cmd = 
case @lDataType
when 61 then
--'update #TempData set ColData=(select convert(varchar(255), ' + @sCurrentCol + ', 126) from $(TableName) where $(UniqueCol)=' + convert(varchar, @RowID) + ') where sName=''' + @sCurrentCol + ''''
'update #TempData set ColData=(select convert(varchar(255), ' + @sCurrentCol + ', 126) from (select ' + @sCurrentCol + ', rownumber = row_number() over (order by $(UniqueCol)) from $(TableName)) a where a.rownumber = ' + convert(varchar, @lCurrentSourceRow) + ') where sName=''' + @sCurrentCol + ''''
else
--'update #TempData set ColData=(select ' + @sCurrentCol + ' from $(TableName) where $(UniqueCol)=' + convert(varchar, @RowID) + ') where sName=''' + @sCurrentCol + ''''
'update #TempData set ColData=(select ' + @sCurrentCol + ' from (select ' + @sCurrentCol + ', rownumber = row_number() over (order by $(UniqueCol)) from $(TableName)) a where a.rownumber = ' + convert(varchar, @lCurrentSourceRow) + ') where sName=''' + @sCurrentCol + ''''
end

--print @cmd
exec (@cmd)

-- get the next row from the cursor.
FETCH next from curColumns into @sCurrentCol, @lDataType
end

-- set our null value text
UPDATE #TempData set
ColData = --'null'
case 
when ColData is null then 'null'
else '''' + replace(ColData, '''', '''''') + ''''
end
where ColData is null or bUseTicks=1

-- initialize the output line, depending on if this is the 1st line or not.
if (@lSrcRowCount = 1)
begin
set @sOutputLine = @tab + @tab + '('
end else begin
set @sOutputLine = @tab + @tab + ', ('
end

-- build our output line of data
SELECT @sOutputLine = @sOutputLine 
+ case when a.rownumber > 1 then ', ' else '' end
+ a.ColData
from (
select *
, rownumber = row_number() over (order by lColOrder)
from #TempData b	
--where (b.bIsIdentity = 0 or $(IncludeIdentity) = 1)
) a
order by a.lColOrder

-- tack on a close parenthesis at the end.
set @sOutputLIne = @sOutputLine + ')'

-- output the line we created.
print @sOutputLine



-- increment our source row index
set @lCurrentSourceRow = @lCurrentSourceRow + 1
end


-- close & deallocate the columns cursor
CLOSE curColumns
DEALLOCATE curColumns

---- close & deallocate the source data cursor
--CLOSE curSourceData
--DEALLOCATE curSourceData


set @sOutputLine = @tab + ') as src('
SELECT @sOutputLine = @sOutputLine
+ case when a.lColOrder > @lFirstColOrder then ', ' else '' end
+ '[' + a.sName + ']'
from #TempData a
order by a.lColOrder

-- close the CTE portion of the output
set @sOutputLine = @sOutputLine + ')' + @crlf + ')'
print @sOutputLine
print '-- select * from cteSrc'

-- build the merge statement
print 'merge into $(TableName) as dest -- target'
print 'using cteSrc as src on dest.$(UniqueCol) = src.$(UniqueCol) -- source'
print 'when matched then -- on match update'
print 'update set '

-- build the update part of the merge statement
set @sOutputLine = ''
SELECT @sOutputLine = @sOutputLine
+ case when a.RowNumber = 1 then @tab else @crlf + @tab + ', ' end
+ 'dest.[' + a.sName + '] = src.[' + a.sName + ']'
from (
select *
, RowNumber = row_number() over (order by lColOrder)
from #TempData b
where b.sName = '$(UniqueCol)'
) a
order by a.lColOrder
print @sOutputLine

-- build the insert part of the merge statement
print 'when not matched then -- insert missing'

-- create the comma separated list of columns.
set @sOutputLine = ''
SELECT @sOutputLine = @sOutputLine
+ case when a.RowNumber = 1 then '' else ', ' end
+ '[' + a.sName + ']'
from (
select *
, RowNumber = row_number() over (order by lColOrder)
from #TempData
) a
order by a.lColOrder

-- output the insert column & value list.
print @tab + 'insert (' + @sOutputLine + ')'
print @tab + 'values (' + @sOutputLine + ')'

print '--when not matched by source then delete-- Delete missing from source'
print ';'


-- turn off the identity insert on the table.
print @crlf + 'set identity_insert $(TableName) off'

--turn off the nocount
set nocount off;


--select * from #TempData

---- make sure the temp table does not already exist
--IF (OBJECT_ID('tempdb..#TempData') IS NOT NULL)
--begin
-- DROP TABLE #TempData
--end