October 23, 2013 at 8:19 am
i am trying to setup a process to update table and associated columns specified in admin tables.
admin table have columns tb_name and column_name, flag
ex one table may have one column other table may have 6 columns that need to updated. Base on flag column y/n i am going to update those columns.
Can any one help me out how to write a dynamic update statement in this case ?
thanks for help!!!
October 23, 2013 at 8:24 am
Definitely you wil get help here some stuf is still missing , you havent mentieond that on what basis you will do the update .
and how would you select that which column and which tables need to be picked up ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 23, 2013 at 8:33 am
whatever specified in the adim table like customer and columns fname and lname status as Y
i want to build a update statement whatever specified in admin table
October 23, 2013 at 9:10 am
On these forums you will find lots and lots of people willing and able to help. However, in order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 23, 2013 at 9:33 am
i am new bee let me try to provide actual requirement
Create table dbo.UpdateDef( SourceTableName VARCHAR(100),ColumnName VARCHAR(100),Status_fl CHAR(1))
go
insert into dbo.UpdateDef values ( 'dbo.Src','NAME','Y')
go
insert into dbo.UpdateDef values ( 'dbo.Src','ADDRESS','N')
go
insert into dbo.UpdateDef values ( 'dbo.Src','AGE','Y')
go
insert into dbo.UpdateDef values ( 'dbo.Src1','FName','Y')
go
insert into dbo.UpdateDef values ( 'dbo.Src2','LName','N')
go
insert into dbo.UpdateDef values ( 'dbo.Src2','SSN','Y')
go
insert into dbo.UpdateDef values ( 'dbo.Src','phone','Y')
go
i want to generate update statements as below based on information available in update def table
update Src a
set a.name = replace(a.name,'s','t')
,a.age = replace(a.name,'2','3')
October 23, 2013 at 10:04 am
mxy (10/23/2013)
i am new bee let me try to provide actual requirementCreate table dbo.UpdateDef( SourceTableName VARCHAR(100),ColumnName VARCHAR(100),Status_fl CHAR(1))
go
insert into dbo.UpdateDef values ( 'dbo.Src','NAME','Y')
go
insert into dbo.UpdateDef values ( 'dbo.Src','ADDRESS','N')
go
insert into dbo.UpdateDef values ( 'dbo.Src','AGE','Y')
go
insert into dbo.UpdateDef values ( 'dbo.Src1','FName','Y')
go
insert into dbo.UpdateDef values ( 'dbo.Src2','LName','N')
go
insert into dbo.UpdateDef values ( 'dbo.Src2','SSN','Y')
go
insert into dbo.UpdateDef values ( 'dbo.Src','phone','Y')
go
i want to generate update statements as below based on information available in update def table
update Src a
set a.name = replace(a.name,'s','t')
,a.age = replace(a.name,'2','3')
HUH??? Where do the values inside the replace come from? From what I see you are going to have to use dynamic sql for this. You will probably need to provide ddl for Src as well. Most importantly is you need to provide some details about what it is you are trying to do. Keep in mind that we know only the information you have posted.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 23, 2013 at 11:30 am
thanks for your help
here is the structure of the src tables and other
Create table dbo.src
(
ID int Identity(1,1) PRIMARY KEY CLUSTERED
,Name varchar(100)
,Address varchar(255)
,Phone varchar(25)
,Age INT
)
insert into src values ('sam','main street pa', '123-456-789', 30)
insert into src values ('tim','state street pa', '134-456-789', 20)
Create table dbo.src1
(
ID_scr1 int Identity(1,1) PRIMARY KEY CLUSTERED
,FNAME varchar(100)
,LNAME varchar(255)
)
insert into src1 values ('sam','john')
insert into src1 values ('jim','tim')
insert into src1 values ('kim','tom')
CREATE TABLE dbo.scr2
(
phone varchar(30)
)
insert into scr2 values(234-456-7899)
insert into scr2 values(222-436-7339)
These tables don't have any relationship. the reason i am creating a admin table(updatedef) is to store which columns i need to update. tomorrow i may need to add new column or remove a column. For this i have included a flag Y or N so that i can generate update statement based on Status_fl column. i want to update string columns (name, address , country), is to REPLACE S with t and number columns (age, phone) is to replace 2 with 3.
let me know if you need more info
October 23, 2013 at 12:02 pm
What is the purpose of this? This seems like a very rudimentary method for scrambling your data slightly. Maybe if you could explain what the ultimate goal is we can help you find a far less convoluted way of doing it.
Given what you have posted you will have to examine sys.columns to determine the datatype of each update. This is going to be a lot of work and dynamic sql to even get close. You only mention string and number datatypes. What about dates or other various datatypes? Do you simply skip those?
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 23, 2013 at 12:12 pm
thanks sena . i dont need to worry about rest of the columns in those tables. i can add another column to specify whether its a string or num column in admin table
this is to scramble business sensitive data.
October 23, 2013 at 9:14 pm
Can anyone help on this
!!!!!!!!!!!!!!!!!
October 24, 2013 at 3:59 am
declare @updatedef table ( id int identity, tablename varchar(30), columnname varchar(50), Status_fl bit)
declare @max-2 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-2 = MAX(id) from @updatedef
set @cnt = 1
while (@cnt < = @max-2)
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;-)
October 24, 2013 at 7:14 am
thanks for your help
Bhuvnesh
October 24, 2013 at 8:03 am
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-2 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-2 = MAX(id) from @updatedef
set @cnt = 1
while (@cnt < = @max-2)
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-2 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 24, 2013 at 8:16 am
thanks Much Sean i am going to test it now and let you know
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply