perl and insert

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);
}

- Rich Doughty

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.