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

Rate

5 (1)

Share

Share

Rate

5 (1)