Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

update columns dynamically Expand / Collapse
Author
Message
Posted Thursday, October 24, 2013 3:59 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:36 AM
Points: 2,840, Visits: 3,970
declare @updatedef table ( id int identity, tablename varchar(30), columnname varchar(50), Status_fl  bit)
declare @max int ,@cnt int ,@lstr varchar(200),@tablename varchar(30),@columnname varchar(50)
set nocount on

insert into @updatedef (tablename,columnname,Status_fl )
select 'emp', 'fname',1 union
select 'emp', 'age',1 union
select 'emp', 'Phone' ,1 union
select 'EmpSalary', 'Salary' ,1

select @max = MAX(id) from @updatedef
set @cnt = 1
while (@cnt < = @max)
begin
select @lstr = '',@tablename = '',@columnname = ''
select @columnname = columnname, @tablename = tablename from @updatedef
where id = @cnt and Status_fl = 1
set @lstr = 'UPDATE dbo.' + @tablename + ' SET ' + @columnname + ' = REPLACE(' + @columnname + ' ''S'', ''t'')'
print @lstr
set @cnt = @cnt + 1
end






BUT above code ..need CASE statement too to decide which columns need what kind of replace text.


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1507949
Posted Thursday, October 24, 2013 7:14 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 12:09 PM
Points: 53, Visits: 1,128
thanks for your help
Bhuvnesh
Post #1508034
Posted Thursday, October 24, 2013 8:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 13,082, Visits: 12,547
Do be careful here. The fine code that Bhuvnesh posted will have issues if you have gaps in your identity column.

declare @updatedef table ( id int identity, tablename varchar(30), columnname varchar(50), Status_fl  bit)
declare @max int ,@cnt int ,@lstr varchar(200),@tablename varchar(30),@columnname varchar(50)
set nocount on

insert into @updatedef (tablename,columnname,Status_fl )
select 'emp', 'fname',1 union
select 'emp', 'age',1 union
select 'emp', 'Phone' ,1 union
select 'EmpSalary', 'Salary' ,1

--Notice what happens here when we delete a row and then insert another to generate a gap in the identity
delete @updatedef where id = 4
insert into @updatedef (tablename,columnname,Status_fl )
select 'EmpSalary', 'Salary' ,1

select @max = MAX(id) from @updatedef
set @cnt = 1
while (@cnt < = @max)
begin
select @lstr = '',@tablename = '',@columnname = ''
select @columnname = columnname, @tablename = tablename from @updatedef
where id = @cnt and Status_fl = 1
set @lstr = 'UPDATE dbo.' + @tablename + ' SET ' + @columnname + ' = REPLACE(' + @columnname + ' ''S'', ''t'')'
print @lstr
set @cnt = @cnt + 1
end

Since we are just going to build some dynamic and execute it we can avoid a loop entirely. We can also completely ignore the identity column so gaps there won't be an issue. I also added a new column to @updatedef to indicate if the column is a number type.

declare @updatedef table 
(
id int identity,
tablename varchar(30),
columnname varchar(50),
Status_fl bit,
IsNumber bit
)
declare @max int ,@cnt int ,@lstr varchar(200),@tablename varchar(30),@columnname varchar(50)
set nocount on


insert into @updatedef (tablename,columnname,Status_fl, IsNumber )
select 'emp', 'fname', 1, 0 union
select 'emp', 'age', 1, 1 union
select 'emp', 'Phone' ,1, 1 union
select 'EmpSalary', 'Salary' ,1, 1

delete @updatedef where id = 4
insert into @updatedef (tablename,columnname,Status_fl )
select 'EmpSalary', 'Salary' ,1

;with UpdateValues as
(
select 'update dbo.' + tablename + ' set ' as Prefix,
STUFF((select columnname + ' = replace(' + columnname + case when IsNumber = 1 then ', 1, 2)' else ', ''S'', ''t'')' end + ', '
from @updatedef u2
where u2.tablename = u1.tablename
for XML path('')), 1, 0, '') as UpdateColumns
from @updatedef u1
group by tablename
)

select Prefix + left(UpdateColumns, LEN(UpdateColumns) - 1)
from UpdateValues

This will generate the update statements you need to execute. I would just run this, then copy the output and paste it into a new window to run it.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1508059
Posted Thursday, October 24, 2013 8:16 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 12:09 PM
Points: 53, Visits: 1,128
thanks Much Sean i am going to test it now and let you know
Post #1508073
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse