I have encountered problems using such a method in an automated fashion.

First off, you have to have acess to the same storage as the
firebird/interbase server. Which means you need to set up a seperate server
to listen for requests and then to transfer the created files into the right
directory. Most OS of course have file transfering available over the
network, but if you want to create a secure method of doing so over the
internet from a client, you need to create seperate listening server for it.
Coding/Procuring something like that is not really a big deal, but it just
adds one more layer of complexity. I tried a FTP service with SSH.

Second Firebird/Interbase create a file lock on the external table file once
the SQL statement used to create the external table is committed. If you
attempt to delete this file after the "Bulk" insert statement it will fail.
Even if you delete the external table from the metadata, the file lock is
not released. I have found that only stopping the service itself will
release the lock. This effectively prevents you from using one external
table "BOBS_EXTERNAL_TABLE" and then just changing the file it links to.

So if you were to try to use this to insert large amounts of rows (500K+) on
a daily basis into multiple different databases on a server, you would have
to schedule some down time to clean up files. You would then also need to
record the names of the external tables in your database, since you cannot
use just one name.

All in all, not a really good way to automate large amounts of inserts from
a client.

-----Original Message-----
From: spgilmore [mailto:spgilmore@...]
Sent: Tuesday, January 25, 2005 9:55 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Bulk inserts in Interbase




--- In firebird-support@yahoogroups.com, Milan Babuskov <milanb@k...>
wrote:

> spgilmore wrote:
> > I want to know how to do a bulk-insert in Interbase 6.0 or

Firebird

> > 1.x. I get the impression that BDE will do it, but I am not

using

> > BDE. I have been using the Interbase components in Delphi 7, but
> > would like to be able to do it from ISQL like I do with MSSQL, or
> > even the Interbase API.
> >
> > I've searched Google, IBPhoenix, and all the IB6 PDF

documentation.

> >
> > Does Interbase support this at all? I see lots of documents
> > describing how to speed up bulk inserts, so I suppose it is. But
> > nobody says how to do it!
>
> Currently, only way to do real bulk inserts is to use external

tables. Prepare

> an external table (textual file), declare it in database, and do a:
>
> insert into real_tables
> select * from external_table;
>
> Works really fast.
>
> You can learn details about external tables in IB6 documentation.
>
> --
> Milan Babuskov
> http://fbexport.sourceforge.net
> http://www.flamerobin.org

Excellent! I think that this is just what I was looking for. I'm
going to try it and if it works, I'll recommend that IBPhoenix put
this post in their FAQ. Thank you very much.






Yahoo! Groups Links