Update all Columns in a table with single value

 I have one table with N columns and I need to update all columns  with single value, How can update?
Need to repeat column names ?


DECLARE @CNT INT
DECLARE @INCR INT
DECLARE @STR FLOAT
DECLARE @QUERY VARCHAR(8000)

SET @INCR =1
SET @STR =0.005 --UPDATE VALUE
SELECT @CNT=COUNT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='EMPSAL1' --TABLE NAME

WHILE @INCR<=@CNT
BEGIN
 SELECT @QUERY= COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME='EMPSAL1' AND ORDINAL_POSITION=@INCR
SET @QUERY ='UPDATE EMPSAL1 SET ' +@QUERY+ '=' +CAST(@STR AS VARCHAR)
EXEC(@QUERY)
SET @INCR=@INCR+1
END

1 comment: