>From what I can see in the PostgreSQL manual, multi-row INSERT syntax works in 8.2 and is not supported in 8.1, which you said you're running.
Compare docs for INSERT: http://www.postgresql.org/docs/8.1/static/dml.html#DML-INSERT http://www.postgresql.org/docs/8.2/static/dml-insert.html Are you certain that when you ran the statement successfully against Pgsql, it was running on an 8.1 server? Another good practice is to use explicit transactions to insert your rows as one commit. Otherwise it implicitly creates a new transaction for each statement and commits it, which is overhead multiplied by thousands if you're executing a separate SQL statement per row you insert. Here's some code I just ran against my Pgsql server: $db->beginTransaction(); $stmt = $db->prepare('INSERT INTO foo (col1, col2, col3) VALUES (?, ?, ?)'); for ($i = 0; $i < 1000; $i++) { $stmt->execute(array($i, $i, $i)); } $db->commit(); Another thing to look into if you have many thousands of rows to insert. There are usually other solutions to do bulk-loading that are much more efficient than INSERT. These are not standard SQL, but you may be able to use them anyway. For example, PostgreSQL has the COPY statement: http://www.postgresql.org/docs/current/static/sql-copy.html MySQL has the LOAD DATA INFILE statement: http://dev.mysql.com/doc/refman/5.0/en/load-data.html Regards, Bill Karwin ________________________________ From: Camilo Usuga Ortiz [mailto:[EMAIL PROTECTED] Sent: Thursday, October 04, 2007 10:35 AM To: 'redphantm'; [email protected] Subject: RE: [fw-general] RE: About massive inserts Hi, Well, the problem is that we already tried the for and an insert per record, but it works fine with less that 1000 records, after that the server just died and stopped inserting, I agree with you on that we need to keep the simplicity of the code, but I also think that it would be a lot faster to create only 1 insert that several thousands of inserts, what do you think? Camilo -----Original Message----- From: redphantm [mailto:[EMAIL PROTECTED] Sent: Thursday, October 04, 2007 12:32 PM To: [email protected] Subject: Re: [fw-general] RE: About massive inserts That's odd. Well, if you "have" to make a connection for each then one big one would be better. I would suggest creating a persistent connection, and then inserting one by one for simplicities sake. Shekar C Reddy wrote: > > For individual inserts, what if the dB has to make a connection for each > insert? Sending all inserts as a single string may need just one > connection. > > > > > On 10/4/07, redphantm <[EMAIL PROTECTED]> wrote: >> >> >> I would just use a for loop. I believe that would keep the code simple, >> and >> simplicity I think is the main goal for the Zend Framework. I think the >> phrase is "simplicity over regularity." I don't think you will even be >> able >> to notice the difference from one huge insert query and many single >> insert >> queries. >> >> Bill Karwin wrote: >> > >> > Right; the Adapter's insert() method accepts only one tuple in its >> > associative array argument. If you are doing an extended insert for >> > many rows, you should format the full SQL statement as a string, and >> run >> > it with the Adapter's query() method. >> > >> > $sql = "INSERT INTO myTable ( id, field_1, date ) VALUES >> > >> > ( 301, 40971, '2007-10-04 04:00:19' ), >> > >> > ... >> > >> > ( 301, 40990, '2007-10-04 04:00:19' )"; >> > >> > >> > >> > $db->query($sql); >> > >> > >> > >> > Note that you can ignore the Statement object returned by this method >> in >> > this case. The query() method is just a shortcut for prepare() and >> > execute(). It doesn't have to be a SELECT query, it can be any other >> > SQL statement type (provided the statement supports being prepared). >> In >> > this case you have no need to execute this statement more than once, so >> > you don't need to save the Statement object returned from query(). >> > >> > >> > >> > Perhaps there is a need for a new method insertMulti() or something >> like >> > that, which would accept an array of associative arrays, and format the >> > SQL appropriately. Feel free to submit a JIRA issue for this. >> > >> > >> > Regards, >> > Bill Karwin >> > >> > >> > ________________________________ >> > >> > From: Camilo Usuga Ortiz [mailto:[EMAIL PROTECTED] >> > Sent: Thursday, October 04, 2007 7:52 AM >> > To: [email protected] >> > Subject: [fw-general] About massive inserts >> > >> > >> > >> > >> > >> > Hi All, >> > >> > >> > >> > We are currently developing an application where we need to >> > insert a big amount of data (thousands of records), we are connecting >> to >> > a PostgreSQL (8.1.6) database through PDO (pdo_pgsql ) the query looks >> > pretty much like this: >> > >> > >> > >> > INSERT INTO myTable ( id, field_1, date ) VALUES >> > >> > ( 301, 40971, '2007-10-04 04:00:19' ), >> > >> > ( 301, 40972, '2007-10-04 04:00:19' ), >> > >> > ( 301, 40973, '2007-10-04 04:00:19' ), >> > >> > ( 301, 40974, '2007-10-04 04:00:19' ), >> > >> > .... >> > >> > .... >> > >> > .... >> > >> > .... >> > >> > ( 301, 40990, '2007-10-04 04:00:19' ); >> > >> > >> > >> > It works when we execute the query directly on the database, but >> > when using the _db methods like query, insert or even fetchRow, we >> > always get the same error: Syntax error: 7 ERROR: syntax error at or >> > near "," LINE 1: ...ate ) VALUES ( 303, 41471, '2007-10-04 04:30:45' ), >> > ( 303, 4... >> > >> > >> > >> > So the question is, what would be the best option to insert this >> > information? Seems that the insert method does not support associative >> > arrays to load all of the info there. >> > >> > >> > >> > Thanks a lot for the help! >> > >> > >> > >> > Camilo >> > >> > >> > >> > >> > >> > >> > >> >> -- >> View this message in context: >> http://www.nabble.com/About-massive-inserts-tf4569375s16154.html#a130447 52 >> Sent from the Zend Framework mailing list archive at Nabble.com. >> >> > > -- View this message in context: http://www.nabble.com/About-massive-inserts-tf4569375s16154.html#a130448 71 Sent from the Zend Framework mailing list archive at Nabble.com.
