September 26, 2007 at 4:34 pm
Greetings,
I'm a little new to this and could use some help
I have a database where several tables have a column called FilterNumber (about 35 Tables). Not all tables have this column. I need to update a specific value in each one of these columns for the entire database. Can I do this without writing out the same update statement 35 times and changing the Table Name. The Logic would be to read in each Table name in the database where FilterNumber exists as a column (Loop through all Tables in database) and update this column if it exists. Thank you
September 26, 2007 at 4:44 pm
Paul Stasny (9/26/2007)
Greetings,I'm a little new to this and could use some help
I have a database where several tables have a column called FilterNumber (about 35 Tables). Not all tables have this column. I need to update a specific value in each one of these columns for the entire database. Can I do this without writing out the same update statement 35 times and changing the Table Name. The Logic would be to read in each Table name in the database where FilterNumber exists as a column (Loop through all Tables in database) and update this column if it exists. Thank you
Run this script, Take the output as text, paste it on a query window and run it
declare @value int
set @value = 5
select 'update ' + table_name + ' set ' + column_name + ' = ' + Cast(@value as varchar (20)) + char(13)+char(10) + 'go' + char(13)+char(10)
from information_schema.columns
where column_name = 'FilterNumber '
Now I don't recomend this as a way to deal with data problems ...
Cheers
* Noel
September 26, 2007 at 5:33 pm
What do you recomment then?? ;).
September 26, 2007 at 8:56 pm
Well, I have to agree with noeld's reply. His suggested script will do what you ask, but you have to account for the possibility that someone someday could add a column by the same name that has nothing to do with this process. That's going to be a tough bug to find.
I suggest that you statically define the tables which should be update, and run some (admittedly very ugly) dynamic SQL to run through the tables you specify and update the values. Something like the snippet below should help you out:
------------------------------------------
-- This table variable will store your list of tables
declare @tables table
(
row_id int identity(1,1)
, table_name varchar(50)
)
-- Insert the names of your tables to be updated into this table variable
insert @tables values ('table1')
insert @tables values ('table2')
insert @tables values ('table3')
-- Control variable
declare @rownum int
set @rownum = 0
-- Loop through and update each table. Be sure to modify the @sql variable
while @rownum < (select count(*) from @tables)
begin
set @rownum = @rownum + 1
declare @sql nvarchar(500)
set @sql = 'update ' + (select table_name from @tables where row_id = @rownum) + ' set FilterNumber = ''some value here'''
exec sp_executesql @sql
end
--------------------
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
September 26, 2007 at 9:26 pm
Same problem... this is a case where you can use scripts to help you figure out all the possible needed changes... but you need to manually authorize them to be 100% safe. This is definitly not something I would want to automate 100%... or I would have the authorities sign a disclaimer over to me on this one saying that I'm not responsible if the db breaks.
September 26, 2007 at 10:42 pm
Thank you everyone for your help. The application is static so I don't have to worry about the table structure changing or new tables being added. I think I can use this to create a Cursor to loop through all the tables in the Information_Schema.Columns View where the Column I want exists and then use an Update Statment on the Column.
September 27, 2007 at 1:11 pm
I tried the code sample below and it prints back the list of Update statements properly. When I copy and paste this into a SQL Window it fails on the Go statement for each line. If I remove the Go statement and manually add it, it works so the problem seems to be the control characters. Any thoughts? Thanks
declare @value int
set @value = 5
select 'update ' + table_name + ' set ' + column_name + ' = ' + Cast(@value as varchar (20)) + char(13)+char(10) + 'go' + char(13)+char(10)
from information_schema.columns
where column_name = 'FilterNumber
September 27, 2007 at 1:15 pm
Try it without the Go statement. Should work fine.
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
September 27, 2007 at 1:16 pm
Ya, ignore the go, output to text results, copy, paste, exec, move on to something more important. That works all the time, everytime.
September 27, 2007 at 3:49 pm
The problem with these kind of scripts is that a little mistake is paid hard BIG time. So I just wanted to warn the poster 😉
* Noel
September 27, 2007 at 4:07 pm
Just consider him re-re-re-re-warned about this.
TAKE BACKUP FIRST.
Looks like the editor is crapping on me.
Anyone knows what going on?
September 28, 2007 at 12:41 pm
Anyone knows what going on?
B U G S
:hehe:
* Noel
September 28, 2007 at 3:45 pm
Ouch!
Noted and added to the list.
September 28, 2007 at 10:16 pm
Always the small details that gets us :hehe:.
September 29, 2007 at 9:31 pm
Steve Jones - Editor (9/28/2007)
Ouch!Noted and added to the list.
Heh... when are they going to get to the list I submitted, Steve?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply