Re: perl and insert

In article <20050517161956.GA23179@europa.cosmos.opusvl.com>,
Rich Doughty <rich@opusvl.com> writes:

> On 17 May 2005, Hrishikesh Deshmukh wrote:
>> Hi All,
>>
>> Anybody knows how to use perl dbi to read a file line by line and
>> insert into db!
>> The books which i have tell you exclusively on running queries.

> it depends on what you need to achieve, but a good place to start would be
> something like:

>    while (my $line = <FILE>)
>    {
>       $dbh->do ('INSERT INTO table (line) VALUES (?)', undef, $line);
>    }

> Where FILE is your open filehandle, and $dbh is your DBI connection, and
> you've modified the SQL as necessary.

> If performance is an issue, you may want to try this (although the
> performance gains depend on database you're using)

>    my $st = $dbh->prepare ('INSERT INTO table (line) VALUES (?)');

>    while (my $line = <FILE>)
>    {
>       $st->execute ($line);
>    }

If there are many lines in the file, Hrishikesh might be better off
using COPY instead of INSERT.  "perldoc DBD::Pg" says how to do that.