Generate Upsert Script

DECLARE@SourceTable sysname,
@TargetTable sysname,
@JoinField sysname,
@SQL VARCHAR(8000),
@ColCount INT,
@Counter INT,
@ColName sysname,
@ColString VARCHAR(2000)

SETNOCOUNT ON

SET@SourceTable = 'tblUpsertTemp'
SET@TargetTable = 'tblUpsert'
SET@JoinField = 'UpsertID'

-- Get Column Names

IF OBJECT_ID('tempdb..#Col') > 0
DROP TABLE #Col

SELECTIDENTITY(INT,1,1) AS ColID,
Name AS ColName,
CASE
WHEN (status & 128) = 128 THEN 1
ELSE 0
END AS IsIdentity
INTO#Col
FROMsyscolumns
WHEREobject_name(id) = 'tblPrizeDraw'
ORDER BY ColID

SET@ColCount = @@ROWCOUNT
SET@Counter = 1

PRINTREPLICATE('-',100)
PRINT'-- Update ' + @TargetTable
PRINTREPLICATE('-',100)

SET@SQL = 'UPDATE' + CHAR(9) + @TargetTable + CHAR(13) + 'SET'
WHILE@Counter <= @ColCount
BEGIN
SELECT@ColName = ColName
FROM#Col
WHEREColID = @Counter

IF@ColName != @JoinField
BEGIN
SET@SQL = @SQL + CHAR(9) + @TargetTable + '.' + @ColName + ' = ' + @SourceTable + '.' + @ColName + ',' + CHAR(13)
END

SET@Counter = @Counter + 1
END

--Remove trailing comma & carriage return
SET@SQL = LEFT(@SQL,LEN(@SQL)-2)

SET@SQL = @SQL + CHAR(13) + 'FROM' + CHAR(9) + @SourceTable
+ CHAR(13) + 'INNERJOIN ' + @TargetTable
+ CHAR(13) + 'ON' + CHAR(9) + @SourceTable + '.' + @JoinField + ' = ' + @TargetTable + '.' + @JoinField

PRINT@SQL

PRINT ''
PRINT REPLICATE('-',100)
PRINT'-- Insert ' + @TargetTable
PRINT REPLICATE('-',100)

SET@SQL = ''
SET@Counter = 1
-- Check for an Identity Column

IF (SELECT COUNT(1) FROM #Col WHERE IsIdentity = 1) = 1
BEGIN
SET@SQL = 'SETIDENTITY_INSERT ' + @TargetTable + ' ON'
PRINT@SQL
END

SET@ColString = ''
SELECT@ColString = @ColString + '[' + ColName + '],'
FROM#Col

SELECT@ColString = LEFT(@ColString,LEN(@ColString)-1)
--PRINT@ColString

SET@SQL = 'INSERT  ' + @TargetTable + '(' + @ColString + ')' + CHAR(13)
SET@SQL = @SQL + 'SELECT'
WHILE@Counter <= @ColCount
BEGIN
SELECT@ColName = ColName
FROM#Col
WHERE ColID = @Counter

SET@SQL = @SQL + CHAR(9) + @SourceTable + '.' + @ColName + ',' + CHAR(13)

SET@Counter = @Counter + 1
END

--Remove trailing comma & carriage return
SET@SQL = LEFT(@SQL,LEN(@SQL)-2)

SET@SQL = @SQL + CHAR(13)
+ 'FROM' + CHAR(9) + @SourceTable
+ CHAR(13) + 'LEFTOUTER JOIN ' + @TargetTable
+ CHAR(13) + 'ON' + CHAR(9) + @SourceTable + '.' + @JoinField + ' = ' + @TargetTable + '.' + @JoinField
+ CHAR(13) + 'WHERE   ' + @TargetTable + '.' + @JoinField + ' IS NULL'

PRINT@SQL

IF (SELECT COUNT(1) FROM #Col WHERE IsIdentity = 1) = 1
BEGIN
SET@SQL = 'SETIDENTITY_INSERT ' + @TargetTable + ' OFF'
PRINT@SQL
END

--SELECT * FROM #Col
DROP TABLE #Col

SET NOCOUNT OFF