keithbas has asked for the wisdom of the Perl Monks concerning the following question:

Is it possible to insert into a table the information from a multidimentional array in one command? That is is there a way to create a set of data that is in the same format of a table in a DB and then insert the whole array at once?

Replies are listed 'Best First'.
Re: DBI Multidimensional array
by belg4mit (Prior) on Apr 24, 2002 at 12:31 UTC
    Why the insistance on "at once"? Do you mean in a single SQL statement, or a single line of code? How many dimensions? For 2 dimensions it is certainly possible with a single line of code and multiple invocations of a SQL statement (for). If this is not data you are intending to query by (and off hand I don't see how you intend to, RDBMS are not meant for this, or at least for what I am interpreing your vague description as), you could use some form of serialization and store the result as a BLOB.

    --
    perl -pew "s/\b;([mnst])/'$1/g"

[reply]
Re: DBI Multidimensional array
by ehdonhon (Curate) on Apr 24, 2002 at 15:21 UTC

    Although it isn't directly supported in DBI, some SQL Systems support the use of the 'COPY' directive which permits you to insert multiple rows of data with a single query. Its supposed to be more efficient than doing a bunch of inserts iteratively. If the database you are using supports this, you may want to look into it.

[reply]
Re: DBI Multidimensional array
by perlplexer (Hermit) on Apr 24, 2002 at 12:33 UTC
    I don't think so.
    Why would you want to do that? Just because you want your program to be shorter?

    --perlplexer
[reply]
Re: DBI Multidimensional array
by graff (Chancellor) on Apr 24, 2002 at 20:52 UTC
    I know that both MySQL and Oracle provide command-line utilities that support "bulk" loading into a table from a text file (sqlload in Oracle, mysqlimport). Presumably other RDBM's have similar tools.

    It is certainly true these utilities will operate a lot faster than looping over a large set of rows with an "insert" query in DBI, even when it's a prepared query with parameters.

    So this would make it worthwhile, if appropriate within your application, to just dump your row-by-column array into a suitable text file, and run the appropriate import command in a "system()" or "exec()" or whatever.

[reply]
A reply falls below the community's threshold of quality. You may see it by logging in.

Back to Seekers of Perl Wisdom