Myles Wakeham wrote:

> I have to copy a series of about 40 tables from an ODBC data source into a
> Firebird SQL database everynight at 12 midnight. This is a 'Data Warehouse'
> dump of information from a transactional system to Firebird. All data in
> Firebird that was there before, can be completely deleted but I'd like to
> keep the table structure intact.

One approach is to do a metadata backup & restore, then load your data
into the newly created database. That's probably the simplest. If
there is some permanent data, you could use one of the copy/load
programs to transfer it to the new database. This has the advantage
that any trigger that guard against invalid data still work. The
disadvantage is that you're loading indexes incrementally.

A more complicated, more efficient process would be to take a metadata
extract of the database and break it into two halves - the first part
creates the database, the domains and the tables. The second half
creates constraints and indexes. Run the first half, load the data,
then run the second half.

Deleting all the data then reusing the database is likely to be slow. A
Firebird delete doesn't actually remove the record. It stores a stub
record saying "this one is deleted and I did it". Eventually, somebody
stumbles on the record and says, "Hunh, this one is deleted and the guy
who did it is committed and so are all his contemporaries", then remove
the record, its back versions (if any) and index entries. You don't
care about any of that, but it will all happen if you try reusing the
database.

Regards,


Ann