Rookie: Constructing a large SQL INSERT statement

[sdfgsd]

Disclaimer: I've googled and searched Perldoc/CPAN.

The format of the INSERT statement is:

INSERT INTO mytable (field_1, field_2, field_3)
VALUES (value_1, value_2, value_3),
(value_1, value_2, value_3),
(value_1, value_2, value_3),
(value_1, value_2, value_3),
etc. ;

In Delphi I just keep looping through the constrution process and keep
adding to a string variable like this:

sqlstatement := sqlstatement + newrow;

Is this the right approach to take in perl? Just keep adding to a scalar, or
would some sort of list/hash construct be better?

Thanks in advance,
JD

 

[Gregory Toomey]

It was a dark and stormy night, and sdfgsd managed to scribble:

Disclaimer: I've googled and searched Perldoc/CPAN.

The format of the INSERT statement is:

INSERT INTO mytable (field_1, field_2, field_3)
VALUES (value_1, value_2, value_3),
(value_1, value_2, value_3),
(value_1, value_2, value_3),
(value_1, value_2, value_3),
etc. ;

In Delphi I just keep looping through the constrution process and keep
adding to a string variable like this:

sqlstatement := sqlstatement + newrow;

Is this the right approach to take in perl? Just keep adding to a scalar,
or would some sort of list/hash construct be better?

Thanks in advance,
JD

Oracle does not have multi-row inserts; mysql does.

The perl equivalent to sqlstatement := sqlstatement + newrow is
$sqlstatement.= $newrow;

While this works, I would probably not insert more than a few dozen rows at once with each insert. I'm not sure on how big the buffers are in your DMBS.

gtoomey

 

[James Willmore]

Disclaimer: I've googled and searched Perldoc/CPAN.

The format of the INSERT statement is:

INSERT INTO mytable (field_1, field_2, field_3)
VALUES (value_1, value_2, value_3),
(value_1, value_2, value_3),
(value_1, value_2, value_3),
(value_1, value_2, value_3),
etc. ;

In Delphi I just keep looping through the constrution process and
keep adding to a string variable like this:

sqlstatement := sqlstatement + newrow;

Is this the right approach to take in perl? Just keep adding to a
scalar, or would some sort of list/hash construct be better?

PL/SQL? SQL92? I'm a little fuzy on what you're trying to do.

What I got out off your post is this - you want to do multiple
inserts. Using the DBI module, you have several ways to do it. The
"quick and dirty" way to do it is to use a here doc and use the 'do'
DBI method (not to be confused with the 'do' function). You could
also build a string and then use the DBI 'prepare' and execute'
methods. You could also 'prepare' a simple SQL statement, loop
through what you want to insert and 'execute' each value (or values).

You should look over the DBI documentation to get a feel for what it
can do - post if you have specific questions on what you read.

HTH


--
Jim

Copyright notice: all code written by the author in this post is
released under the GPL. http://www.gnu.org/licenses/gpl.txt
for more information.

a fortune quote ...
A large number of installed systems work by fiat. That is, they
work by being declared to work. -- Anatol Holt

 

[Tore Aursand]

The format of the INSERT statement is:

INSERT INTO mytable (field_1, field_2, field_3)
VALUES (value_1, value_2, value_3),
(value_1, value_2, value_3),
(value_1, value_2, value_3),
(value_1, value_2, value_3),
etc. ;

Is this valid SQL? The reason I'm askin is that I really don't know,
because I've never tried to do INSERT queries like the one above.

It seems to me, however, that you should do this in a loop, bind'ing the
data to into the query. Example:

my $stInsert = $dbh->prepare('INSERT INTO mytable
(field_1, field_2, field_3)
VALUES (?, ?, ?)');
while ( ... ) {
$stInsert->execute( $value_1, $value_2, $value_3 );
}
$stInsert->finish();

This really depends on how you have chosen to structure the data you're
about to insert into the database. Some more information about that would
cause us to give you a better advice.

 

[sdfgsd]

Disclaimer: I've googled and searched Perldoc/CPAN.

The format of the INSERT statement is:

INSERT INTO mytable (field_1, field_2, field_3)
VALUES (value_1, value_2, value_3),
(value_1, value_2, value_3),
(value_1, value_2, value_3),
(value_1, value_2, value_3),
etc. ;

Thanks for the responses.

The above insert statement is used for large, single inserts into a MySQL
table. Instead of looping over an INSERT statement a few thousand times, it
is easier to construct the statement as the data is being generated, THEN
deliver it to the database.

In Delphi I just built one big string and sent it to the db. Being new to
perl, I'm just not sure what the best is to deliver this statement.

Thanks again for any additional thoughts.

 

[Tore Aursand]

The above insert statement is used for large, single inserts into a
MySQL table. Instead of looping over an INSERT statement a few thousand
times, it is easier to construct the statement as the data is being
generated, THEN deliver it to the database.

Is it? For me it is the other way around, really;

1. Gather the data.
2. Eventually do some processing before handing the data
over to the database.
3. Insert the data into the database.
4. Cleanup.

Seems like your way to do it combines #1 and #3, thus making it hard to do
#2.

Either way, it's quite trivial to do this in Perl. Let's say that you
have gathered your data as an array of arrays and are ready to insert them
into the database;

my $stInsert = $dbh->prepare( 'INSERT INTO table
(field_1, field_2, field_3)
VALUES (?, ?, ?)' );
foreach $record ( @$data ) {
$stInsert->execute( @$record );
}
$stInsert->finish();

Pretty clean to me.

 

[nobull]

Is this valid SQL?

It's certainly not supported on at least one dialect of SQL (MSSQL).

It seems to me, however, that you should do this in a loop, bind'ing the
data to into the query. Example:

my $stInsert = $dbh->prepare('INSERT INTO mytable
(field_1, field_2, field_3)
VALUES (?, ?, ?)');
while ( ... ) {
$stInsert->execute( $value_1, $value_2, $value_3 );
}

Better still use the execute_array() method.

In most current DBD drivers this is just the same as the above loop
but may in future be more efficient.

$stInsert->finish();

Unnecessarly calling finish() on DBI statement handles is, IMHO, a bad
habit.

For an explaination of the situations in which it is necessary see the
documentation of the finish() method in the DBI documentation.

 

[Tore Aursand]

Unnecessarly calling finish() on DBI statement handles is, IMHO, a bad
habit.

Really? Hmm. I started using DBI about 5 years ago, and I remember
having a problem with "something" which didn't go away until I added the
call to those finish() methods.

Anyway. Why is it bad? I still go for the "slow, but safe" approach when
programing, and find myself constantly doing prepare(), execute() and
finish() even when a do() would have been enough.

Oh, well.

 

[James Willmore]

Really? Hmm. I started using DBI about 5 years ago, and I remember
having a problem with "something" which didn't go away until I added
the call to those finish() methods.

Anyway. Why is it bad? I still go for the "slow, but safe"
approach when programing, and find myself constantly doing
prepare(), execute() and finish() even when a do() would have been
enough.

For the record - it's not _required_ to use 'finish' or 'disconnect',
but it's also not frowned upon.

The reason it's not _required_ is simple - once the script ends, the
database object is torn down - taking the database connection with it.
To 'finish' and 'disconnect' is redundant. However, you may want to
tear a connection down _before_ the script finishes - which is why the
methods are available.

So - it's 6 of one, half-dozen of another.

HTH

--
Jim

Copyright notice: all code written by the author in this post is
released under the GPL. http://www.gnu.org/licenses/gpl.txt
for more information.

a fortune quote ...
Real programmers don't bring brown-bag lunches. If the vending
<machine doesn't sell it, they don't eat it. Vending machines
<don't sell quiche.

 

[Kåre Olai Lindbach]

Really? Hmm. I started using DBI about 5 years ago, and I remember
having a problem with "something" which didn't go away until I added the
call to those finish() methods.

According to dbi-docs is finish() meant to be used seldomly, and
"indicates that no more data will be _fetched_ from this statement
handle" (my _underline marking_). Usually only needed when one need to
end fetching a select-statement before all rows are fetched.

What I have had problem with is, when connected MS SQL-server,
without "AutoCommit", is that even select-statements need commit(),
while PostgreSQL and others only need this when executing
insert/upgrade and alike statements.

 

[ctcgag]

Disclaimer: I've googled and searched Perldoc/CPAN.

The format of the INSERT statement is:

INSERT INTO mytable (field_1, field_2, field_3)
VALUES (value_1, value_2, value_3),
(value_1, value_2, value_3),
(value_1, value_2, value_3),
(value_1, value_2, value_3),
etc. ;

In Delphi I just keep looping through the constrution process and keep
adding to a string variable like this:

sqlstatement := sqlstatement + newrow;

Is this the right approach to take in perl? Just keep adding to a scalar,
or would some sort of list/hash construct be better?

I know I'm joining the game late here, but what the heck.

First, I'd take a hard look at doing this at all. I've never seen a
performance problem cured by switching from an insert-in-loop into this
type of bulk insert, but I've seen lots of bugs and brittle code result
from the attempt. You will be tying yourself to a (I believe)
MySQL-specific feature, and even worse, by-passing bind variables in doing
so--all for little gain. If I were to resort to such cheesy stunts to try
to squeeze out that last 10% of performance, I'd skip this bulk insert and
go directly to "Load data local infile into mytable..."

Having said that, the more perlish way to form the bulk insert text is:

$sqlstatement .= join ',', @newvalues


Xho