Generate Upsert Script
This script outputs the TSQL code to do perform an 'Upsert'.
It depends on both the source & target tables having the same structure and rows being uniquely identified with a single field.
Three variables need to be updated prior to execution of this script:
@SourceTable: Table containing the data to be upserted
@TargetTable = Table to be upserted to
@JoinField = Unique row identifier
I'm sure this script could be taken further and/or created as a Stored Procedure if required
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 
