Copying tables from one database to another, in Firebird 2.1

I need to copy tables from one database to another in Firebird 2.1, but in some way which is pretty quick and you can run this on console.

I used FBcopy but get the answer from boss that it is too slow. This must be done in more than one transaction because tables are quite big. Any ideas how I can do this?

Answer

A possible solution is to create external tables with the layout of the source table (or at least: the data you want to transfer), one in the source database and another in the target database.

In the source database, you select what you want to transfer from the source table and insert it into the (empty) external table. After that is done, you can copy the file to the other database. If they are on the same server, it is possible to use the same file directly (I haven’t used external tables enough to point out risks beyond the ones pointed out below).

On the target database, you can then select from the external table and insert into the final target table.

When creating and using external tables, keep the following in mind:

• External tables files are a fixed-width binary data format, the documentation I link shows how you can make it look like a fixed-width text format, but it is really not.

• Correct interpretation of character data depends on the character set (both for correct character conversion and for data length), eg UTF8 columns are 4x their declared length. When creating external tables, explicitly declare the character set to avoid issues with – for example – different default character sets between databases.

  The difference between using a single byte character set vs a 4 byte character set will lead to extreme corruption, because a CHAR(100) in a WIN1252 database will be written/read as 100 bytes, but in a UTF8 database, it will be written/read as 400 bytes. So be sure to explicitly use a character set, eg CHAR(100) CHARACTER SET WIN1252.

• Given its uncompressed nature, it is better to use single bytes character sets instead of – for example – UTF8, if your data allows it.

• External tables do not support blobs or arrays.

• You cannot delete or update rows in an external table. To clear it, you need to delete the underlying external file (Firebird will automatically create a new file when necessary).

• It is not possible to define primary key or foreign key constraints, nor indexes on external tables

To be able to use external tables, you need to configure Firebird to allow it to access those files. In firebird.conf of the server, change the ExternalFileAccess setting from its default of None to ExternalFileAccess = Restrict <path>, where path is the folder or a semi-colon separated list of folders where Firebird has access. Read the documentation about this option in the config file!

A (very) small example. Assuming both database are on the same server, and your databases have a CUSTOMER table you want to transfer:

create table customer (
    id integer constraint pk_customer primary key,
    customer varchar(25) character set win1252 not null
)

You then create the equivalent external table:

create table ext_customer external file 'D:dataDBexttablesext_customer.dat' (
    id integer not null,
    customer varchar(25) character set win1252 not null
)

Create this table in both the source database and the target database.

Then in the source database put the customer data in the external table:

insert into ext_customer (id, customer) select id, customer from customer;

Make sure to commit.

In the target database, you can then use the data. For example, if the customer table is currently empty, you could simply do the opposite we did in source:

insert into customer (id, customer) select id, customer from ext_customer;

If you need more control, consider looking at MERGE.