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

Issue Loading Data from Flat File

I have come across a strange bug that I can't get to the bottom of.

I have a source Excel spreadsheet with approx 30000 rows in it, I have saved this out using Excel 2003 as a csv file which contains all of the source rows.

Using a Flat File Source I am only getting approx 15000 rows through, I have re-saved the spreadsheet as csv and it stops at exactly the same point. However, if I use the excel spreadsheet as the source I get all 30000 rows.

I have checked the CSV at the relevant point and can see no reason why it would stop at that particular point.

I am experiencing a similar problem with another source file handled in exactly the same way.

Has anyone else experienced this?

Philip

UPDATE: looks like this could be a problem in Excel, Used a Excel Data source and a Flat File Destination to create the CSV, and my original Data flow now uses all the data

Philip Coupar wrote:

I have come across a strange bug that I can't get to the bottom of.

I have a source Excel spreadsheet with approx 30000 rows in it, I have saved this out using Excel 2003 as a csv file which contains all of the source rows.

Using a Flat File Source I am only getting approx 15000 rows through, I have re-saved the spreadsheet as csv and it stops at exactly the same point. However, if I use the excel spreadsheet as the source I get all 30000 rows.

I have checked the CSV at the relevant point and can see no reason why it would stop at that particular point.

I am experiencing a similar problem with another source file handled in exactly the same way.

Has anyone else experienced this?

Philip

UPDATE: looks like this could be a problem in Excel, Used a Excel Data source and a Flat File Destination to create the CSV, and my original Data flow now uses all the data

I'm working with Philip on this. Basically it looks as though there's problem in the way that Excel saves stuff to csv. Anyone know why?

-Jamie

|||

We are experiencing the same exact problem. The CSV file imports fine using our old DTS Packages just not with the new SSIS packages we have created. I can provide sample csv files if needed.

If you have an update on this, please let me know.

|||Did you see the workaround that Philip posted (above)?

-Jamie