Directly Create SQL Update Statements of Data in a Table

Lakhangarg
Posted by Lakhangarg under Sql Server category on | Views : 1781
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+ @ConditionColumn

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

Comments or Responses

Login to post response