Re: delete five BIG tables and insert new records on same tables

"Alan" <alan_at_erols.com> wrote in message news:2vsj8lF2obk40U1_at_uni-berlin.de...
>
> "PC" <no_at_spam.com> wrote in message

news:cnalii$855$1_at_msunews.cl.msu.edu...
> > I need to delete five BIG tables and insert new records on same tables.
> > Since it involved lot of computing, it will take long. I'm thinking it
> > will take probably couple of days.
> >
> > What I want is minimal down time, if I have to have little down time.
> > I have sql insert scripts to run.
> >
> > Any suggestions?
> > Thanks.
> > -Jay
> >
>
> You haven't really supplied enough information, but here is one technique
> that will work if you don't have a lot of dependencies on these tables:
>
> Create a new, empty table for each of the original tables. Call them
> table_name_NEW. Insert your data into these _NEW tables. Once finished,
> TRUNCATE your original tables and insert into them from these _NEW tables.
> You need to decide to do the truncate/inserts one table at a time, or all
at
> once. This gives you the ability to validate the data before inserting,
> while minimizing data unavailability and less has detrement to performance
> than my next suggestion- if you want zero downtime, and don't mind slow
> performance, just delete and insert in one transaction. Data availability
> is100% Performance, well, maybe not fantastic. There are variations on
> these ideas, but you haven't provided enough information.
>
>

What about renaming the new (and old) tables after the inserts ? This should work also ...

RENAME TABLE <table> TO <oldtable>
/

RENAME TABLE <newtable> TO <table>
/

Matthias Received on Mon Nov 15 2004 - 15:46:24 CST