SSIS auto generate update scripts

Specify the TableName in @TableName variable to create the Update script to be used for SSIS.

DECLARE @counter int;

DECLARE @QueryString nvarchar(2400) = ''

DECLARE @ColumnName nvarchar(120);

DECLARE @TableName nvarchar(120) = 'MyTable'

CREATE TABLE #ColumnList

(

ID int IDENTITY(1,1)

,ColumnName varchar(1200)

)

INSERT #ColumnList

select sys.columns.name from sys.columns

inner join sys.tables on sys.tables.object_id = sys.columns.object_id

where sys.tables.name = @TableName

and sys.columns.name <> 'ID'

SET @counter = 1

SET @QueryString = 'UPDATE dbo.' + @TableName + '

SET '

SET @ColumnName = (SELECT ColumnName FROM #ColumnList WHERE ID=@counter)

SET @QueryString = @QueryString + '

' + @ColumnName+ ' = ? '

SET @counter = @counter + 1;

WHILE @counter <= (SELECT COUNT(*) FROM #ColumnList)

BEGIN

SET @ColumnName = (SELECT ColumnName FROM #ColumnList WHERE ID=@counter)

SET @QueryString = @QueryString + '

,' + @ColumnName+ ' = ? '

SET @counter = @counter + 1;

END

SET @QueryString = @QueryString + '

WHERE ID = ?;'

PRINT @QueryString

DROP TABLE #ColumnList