Directly Create SQL Update Statements of Data in a Table

These Set of statements are used to directly create SQL update statements of data for given table.
we just need to pass the name of the Table. Here in this just replace 'MyTestTable' with your own table name.

if there is any identity Column in your table then that will be used for the where condition otherwise first numeric column will be used for where condition.

DECLARE @TempString VARCHAR(7999),@Tablename varchar(200)
SET @Tablename='MyTestTable'
SET @TempString = ''

SELECT @TempString = @TempString + SYS.Columns.Name+'=''''''+ISNULL('+SYS.Columns.Name+','''')+'''''','
FROM SYS.Columns Inner JOIN SYS.Objects ON SYS.Objects.Object_Id=SYS.Columns.Object_Id
WHERE SYS.Objects.type='u' and SYS.Objects.name=@Tablename and
SYS.Columns.System_type_id IN (239,175,167,231,99,35,34)AND SYS.Columns.IS_Identity=0

SELECT @TempString = @TempString + SYS.Columns.Name+'=''''''+CONVERT(varchar(30),ISNULL('+SYS.Columns.Name+',''1/1/1900''))+'''''','
FROM SYS.Columns Inner JOIN SYS.Objects ON SYS.Objects.Object_Id=SYS.Columns.Object_Id
WHERE SYS.Objects.type='u' and SYS.Objects.name=@Tablename and
SYS.Columns.System_type_id IN (61)AND SYS.Columns.IS_Identity=0

SELECT @TempString = @TempString + SYS.Columns.Name+'=''+CONVERT(varchar(10),ISNULL('+SYS.Columns.Name+',0))+'','
FROM SYS.Columns Inner JOIN SYS.Objects ON SYS.Objects.Object_Id=SYS.Columns.Object_Id
WHERE SYS.Objects.type='u' and SYS.Objects.name=@Tablename and
SYS.Columns.System_type_id NOT IN (239,175,167,231,99,35,34,61)AND SYS.Columns.IS_Identity=0

SET @TempString= SUBSTRING(@TempString,0,LEN(@TempString)-2)

DECLARE @ConditionColumn varchar(200)
SELECT @ConditionColumn=SYS.Columns.name FROM SYS.Columns Inner JOIN SYS.Objects ON SYS.Objects.Object_Id=SYS.Columns.Object_Id
WHERE SYS.Objects.type='u' and SYS.Objects.name=@Tablename and IS_Identity=1

IF (@ConditionColumn=NULL)
SELECT TOP 1 @ConditionColumn=SYS.Columns.name FROM SYS.Columns Inner JOIN SYS.Objects
ON SYS.Objects.Object_Id=SYS.Columns.Object_Id
WHERE SYS.Objects.type='u' and SYS.Objects.name=@Tablename AND
SYS.Columns.System_type_id NOT IN (239,175,167,231,99,35,34,61)

SET @ConditionColumn=' +'' WHERE '+@ConditionColumn+'=''+CONVERT(varchar(10),ISNULL('+@ConditionColumn+',0))'

DECLARE @SQLQuery varchar(Max),@SubQuery varchar(Max)
SET @SubQuery='''UPDATE '+@Tablename+' SET '+@TempString+ ISNULL(@ConditionColumn,'')

SET @SQLQuery= 'SELECT ' +@SubQuery+' FROM '+@Tablename
EXEC (@SQLQuery)