Rookie: Constructing a large SQL INSERT statement

Discussion in 'Perl Misc' started by sdfgsd, Oct 20, 2003.

  1. sdfgsd

    sdfgsd Guest

    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
     
    sdfgsd, Oct 20, 2003
    #1
    1. Advertising

  2. 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
     
    Gregory Toomey, Oct 20, 2003
    #2
    1. Advertising

  3. On Mon, 20 Oct 2003 01:50:08 GMT
    "sdfgsd" <> wrote:

    > 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
     
    James Willmore, Oct 20, 2003
    #3
  4. sdfgsd

    Tore Aursand Guest

    On Mon, 20 Oct 2003 01:50:08 +0000, sdfgsd wrote:
    > 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.


    --
    Tore Aursand <>
     
    Tore Aursand, Oct 20, 2003
    #4
  5. sdfgsd

    sdfgsd Guest

    "sdfgsd" <> wrote in message
    news:kbHkb.185242$...
    > 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.
     
    sdfgsd, Oct 20, 2003
    #5
  6. sdfgsd

    Tore Aursand Guest

    On Mon, 20 Oct 2003 12:23:37 +0000, sdfgsd wrote:
    >> 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. ;


    > 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.


    --
    Tore Aursand <>
     
    Tore Aursand, Oct 20, 2003
    #6
  7. sdfgsd

    Guest

    Tore Aursand <> wrote in message news:<>...
    > On Mon, 20 Oct 2003 01:50:08 +0000, sdfgsd wrote:
    > > 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?


    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.
     
    , Oct 20, 2003
    #7
  8. sdfgsd

    Tore Aursand Guest

    On Mon, 20 Oct 2003 09:43:53 -0700, nobull wrote:
    >> $stInsert->finish();


    > 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. :)


    --
    Tore Aursand <>
     
    Tore Aursand, Oct 20, 2003
    #8
  9. On Tue, 21 Oct 2003 00:54:44 +0200
    Tore Aursand <> wrote:

    > On Mon, 20 Oct 2003 09:43:53 -0700, nobull wrote:
    > >> $stInsert->finish();

    >
    > > 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.


    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.
     
    James Willmore, Oct 21, 2003
    #9
  10. On Tue, 21 Oct 2003 00:54:44 +0200, Tore Aursand <>
    wrote:

    >On Mon, 20 Oct 2003 09:43:53 -0700, nobull wrote:
    >>> $stInsert->finish();

    >
    >> 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.


    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.

    --
    mvh/Regards
    Kåre Olai Lindbach
     
    Kåre Olai Lindbach, Oct 21, 2003
    #10
  11. sdfgsd

    Guest

    "sdfgsd" <> wrote:
    > 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

    --
    -------------------- http://NewsReader.Com/ --------------------
    Usenet Newsgroup Service New Rate! $9.95/Month 50GB
     
    , Oct 28, 2003
    #11
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Kathy Burke
    Replies:
    6
    Views:
    638
    Munsifali Rashid
    Aug 13, 2003
  2. Nathan Sokalski

    SQL syntax error in INSERT INTO statement

    Nathan Sokalski, Jul 1, 2005, in forum: ASP .Net
    Replies:
    5
    Views:
    14,645
    ahmed_samir
    Sep 3, 2010
  3. ddog
    Replies:
    1
    Views:
    2,063
    Thomas Kellerer
    Feb 7, 2007
  4. J. Muenchbourg
    Replies:
    3
    Views:
    250
    Aaron Bertrand - MVP
    Sep 30, 2003
  5. Charles Fitzgerald
    Replies:
    3
    Views:
    101
    Charles Fitzgerald
    Apr 6, 2004
Loading...

Share This Page