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

ISQL: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near ' '

G'day everyone
That's a space between the ticks.
It's all part of a longer script but seeing as the failure occurs on
line 1
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[config]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[config]
GO
That's three lines only. Does it matter that they're in Unicode?
Any ideas?
Kind regards,
Bruce M. Axtens
Software Engineer
Strapper TechnologiesI pasted that code into a .sql file and ran it through isql.exe and it executed without errors. What
version of SQL Server to you connect to?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"axtens" <Bruce.Axtens@.gmail.com> wrote in message
news:1162976105.388119.97910@.b28g2000cwb.googlegroups.com...
> G'day everyone
> That's a space between the ticks.
> It's all part of a longer script but seeing as the failure occurs on
> line 1
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[config]') and OBJECTPROPERTY(id, N'IsUserTable') => 1)
> drop table [dbo].[config]
> GO
> That's three lines only. Does it matter that they're in Unicode?
> Any ideas?
> Kind regards,
> Bruce M. Axtens
> Software Engineer
> Strapper Technologies
>|||Tibor,
Thanks for your reply. I found out from another group that the line
count is reset each time ISQL executes a "GO" statement, so the
offending line could have been anywhere in a 123 line file.
What I ended up doing was creating two scripts from the original: one
to do the "DROP"s and the other to do the "CREATE"s.
Thanks for your time.
Kind regards,
Bruce M. Axtens
Software Engineer
Strapper Technologies