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)
SET NOCOUNT ON
SET @SourceTable = 'tblUpsertTemp'
SET @TargetTable = 'tblUpsert'
SET @JoinField = 'UpsertID'
-- Get Column Names
IF OBJECT_ID('tempdb..#Col') > 0
DROP TABLE #Col
SELECT IDENTITY(INT,1,1) AS ColID,
Name AS ColName,
CASE
WHEN (status & 128) = 128 THEN 1
ELSE 0
END AS IsIdentity
INTO #Col
FROM syscolumns
WHERE object_name(id) = 'tblPrizeDraw'
ORDER BY ColID
SET @ColCount = @@ROWCOUNT
SET @Counter = 1
PRINT REPLICATE('-',100)
PRINT '-- Update ' + @TargetTable
PRINT REPLICATE('-',100)
SET @SQL = 'UPDATE' + CHAR(9) + @TargetTable + CHAR(13) + 'SET'
WHILE @Counter <= @ColCount
BEGIN
SELECT @ColName = ColName
FROM #Col
WHERE ColID = @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) + 'INNER JOIN ' + @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 = 'SET IDENTITY_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) + 'LEFT OUTER 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 = 'SET IDENTITY_INSERT ' + @TargetTable + ' OFF'
PRINT @SQL
END
--SELECT * FROM #Col
DROP TABLE #Col
SET NOCOUNT OFF
