Oh, yes, I thought about that one, but it's slightly impractical: the number of rows to be inserted is in the 10Ks...
And besides, I just tried the LOAD DATA, which works fine! On top of that, LOAD is less finicky than INSERT: it just replaces (or ignores if you set it for that) rather than complaining if a key already exists! I'll take some time to check ddins anyway, but at least i know I have a solution... Thanks! Denis > From: [email protected] > To: [email protected] > Date: Fri, 18 Jun 2010 10:01:20 -0400 > Subject: Re: [Jprogramming] bulk insert with ODBC (into MySQL db) > > Another option is to format a gigantic INSERT ... VALUES statement, like > this: > > DATA =: 3 3 $ ;:'alpha beta gamma delta episilon zeta eta theta iota' > 'INSERT INTO someTable (columnA, columnB, columnC) VALUES ',}: ; <@:(1 > A.'(',,&')')@:;"1|:('"',,&'",')&.>DATA > INSERT INTO someTable (columnA, columnB, columnC) VALUES > ("alpha","delta","eta"),("beta","episilon","theta"),("gamma","zeta","iota") > > Of course, you have to make sure you quote values of different types > (string, char, date, etc) appropriately. > > Having said that, I'm not sure this will be much faster than your current > row-at-a-time insertions. For large data sets, the J formatting, network > transmission, and SQL parsing may overcome any gains from bulk insertion. > Also, you may hit edge conditions on the length of a SQL script your server > will accept. The speed of J formatting we can control to an extent; so if > your expression is too slow, post it, and we can improve it. And if the > server rejects your SQL on length, you could try inserting newlines into the > statement to see if that fixes it. > > Anyway, for practical matters, there's usually more than one way to skin a > cat. But now I'd like to return to my preferred domain: wholly impractical > matters. > > -Dan > > > > ---------------------------------------------------------------------- > For information about J forums see http://www.jsoftware.com/forums.htm _________________________________________________________________ Look 'em in the eye: FREE Messenger video chat http://go.microsoft.com/?linkid=9734386 ---------------------------------------------------------------------- For information about J forums see http://www.jsoftware.com/forums.htm
