On Tue, 2005-08-23 at 09:34 +0100, Lester Caine wrote:

> David Johnson wrote:
>
> > According to SQL92 specifications ...
> >
> > create newtable as
> > select * from oldtable;
> >
> > drop oldtable;
> >
> > But this doesn't seem to work in Firebird. A review of the Interbase
> > documentation suggests that there is no direct method. You will need to
> > create your new table, migrate your data, and then drop the old table.
>
> The main reason it is not allowed is that unlike the simple SQL92 spec,
> we have triggers and stored procedures that may be using the oldtable,
> and we have foreign keys that may be linking to it, all of which need to
> be managed when changing the name.

SQL92 spec includes triggers and SP's. It does not specify how those
are handled in a drop table scenario, leaving that up to the
implementation. The Firebird implementation would prevent the drop
table until the SP/trigger dependencies were changed.

The first statement, however, is precisely construct a new, clean table
and pump the data from the old table, except that it is implemented
inside the database engine. I am curious enough to look and see if it
could not be easily implemented in the Firebird engine, just because it
is so handy for taking snapshot backups of data from tables during
production troubleshooting/resolution.

>
> Personally I like the security of building a clean new database and
> pumping data from the old one. It may take a long time, but it ensures
> that there are no problems with any of the data :)
>

It doesn't take that long - you can sustain 1,000 rows per second using
JDBC, if there is no transformation involved in the middle. Even with
complex transformations, you can still get 250 rows per second easily.