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?>
>

Iterating Data Movement

Hi all

I am wanting to continuously monitor a source table throughout the day and as data becomes available, process it and insert it into one of a number of tables.

I have tried achieving this using a FOR LOOP and setting the halt condition such that it is not stisfiable. However, this has a couple of problems:

1) It runs in a tight loop and consequently degrades system performance enormously.

2) I can't get transactions to work. I would like each iteration of the loop to spawn a new transaction under which the tasks in the loop can run. Therefore, if one of the tasks fails during such an iteration, only the updates affected by that iteration are lost.

Ideally, I would like to be able to put a wait statement within the loop container so that it runs every couple of seconds. And would also like to implement transactions as described above.

All help is appreciated.

Jays :-)

I think you want a trigger on the table so you know when the data in that table is changed, and copy it accordingly.

|||

Hi Christian

I would like to avoid that if I can.

My package has custom logging that writes a set of audit records each time it is run. This would result in a LOT of these records being written throughout the day when one such set would do.

Thanks

Jays :-)