Subject Re: Inserting Rows into DB
Author Adam
--- In, "Richard Thomas"
<rthomas@...> wrote:
> Hi All:
> I have successfully created a Firebird Database, table and am now
ready to
> insert some test data. I've read about the INSERT FROM syntax.
> It appears that the examples insert one row at a time using the VALUES
> clause. In my program I will read from a CSV or Random Access file
or get
> some data for several rows, perhaps 30 to 100 in some other way,
then want
> to insert these new rows into the FB database table. Is there some
> to build multiple VALUES list entries and insert them with one execute
> statement I can research further?

The statement I think you may be thinking of is


Which is similar to a copy-paste operation, but that requires the data
to be already in the database somewhere else. You could use external
tables then such a query to import them all quickly, but for 100
records, it is not worth it.

> Or, do I have to loop through the logic to define the COLUMN VALUES
for each
> row and perform an EXECUTE for each row I want to insert?
> I'm considering performance, of course.

Parameters is what you are after. I do not know how this works in your
development environment, but it should be there somewhere. Basically,
because using different parameter values doesn't require any change to
the plan, it is more efficient than rebuilding the query from scratch
(and a LOT easier to read).

In Delphi, it would be.

qry.sql.text := 'insert into blah (ID) Values (:myid)';

for i := 1 to 100 do
qry.ParamByName('myID').Value := i;

To put your performance worries in perspective, inserting 1000 rows
should be under 1 second (on reasonable hardware). There will be some
overhead depending on which interface is used (in this case ODBC), but
what I am saying is 100 is not much.