Iteration through many tables to perform update

...Ok so I have one field that exists in an many ARCHIVE tables(approx 25).
The field name is the same throughout these tables and each table is created
in weekly intervals.
I want to be able to update the field for all the rows in each of these
table in one lump of an update sProc.
My initial ideas areto query sysobjects for the tables I want and then
iterate through them one by one until the update is complete (maybe using
dynamic sql). This is a once off update and performance time is the key.
Any ideas / examples?>I forgot to add, My table has 10million rows and i estimate an runtime of
5.5hrs. Here's the clincher though. I only have a 5 hr window to complete th
e
update.
"marcmc" wrote:

> ...Ok so I have one field that exists in an many ARCHIVE tables(approx 25)
.
> The field name is the same throughout these tables and each table is creat
ed
> in weekly intervals.
> I want to be able to update the field for all the rows in each of these
> table in one lump of an update sProc.
> My initial ideas areto query sysobjects for the tables I want and then
> iterate through them one by one until the update is complete (maybe using
> dynamic sql). This is a once off update and performance time is the key.
> Any ideas / examples?>
>|||Are all these tables of the same structure, i.e. exact same column names and
data types across all tables? If so, it sounds like a partitioned view.
You could then update the particular column.
Briefly, you create CHECK constraints on the partitioning column on each
table. The partitioning column must be part of the primary key. Then you
create a view like:
create view MyView
as
select * from MyTable1
union all
select * from MyTable2
union all
...
go
update MyView
set
MyCol = 'XYZ'
As for your window, large updates take time. You may want to do a
background iterative method, as long as logical consistency is not an issue.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:8661C481-F4F0-4ED4-97E2-74BB47D70F4A@.microsoft.com...
I forgot to add, My table has 10million rows and i estimate an runtime of
5.5hrs. Here's the clincher though. I only have a 5 hr window to complete
the
update.
"marcmc" wrote:

> ...Ok so I have one field that exists in an many ARCHIVE tables(approx
> 25).
> The field name is the same throughout these tables and each table is
> created
> in weekly intervals.
> I want to be able to update the field for all the rows in each of these
> table in one lump of an update sProc.
> My initial ideas areto query sysobjects for the tables I want and then
> iterate through them one by one until the update is complete (maybe using
> dynamic sql). This is a once off update and performance time is the key.
> Any ideas / examples?>
>