This is what I normally do. I mean I create huge SQL files with a thousands of rows of SQL Insert statements from J and submit this file to our backend service for insertion.
I basically split the operation in parts: 1. SQL DML command generation. [J] 2. Transmit data to the server (Sockets, email, ftp, etc). [C#] 3. SQL execution on the local database server. [DB Local/Builtin facility] I'm just suggesting that maybe you can look at alternative ways of getting your data into the server. -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Dan Bron Sent: Friday, June 18, 2010 10:01 PM To: 'Programming forum' 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 ---------------------------------------------------------------------- For information about J forums see http://www.jsoftware.com/forums.htm
