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
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
great job Job:)nice one..
ReplyDelete