on 05/20/2004 09:36 PM, David Griffiths at [EMAIL PROTECTED] wrote: > You didn't mention the table-type (storage-engine) you were planning on > using, but if you use InnoDB, and do it all in one transaction (ie turn > off auto-commit and commit just once at the endi), then any failure > during the mass-insert will cause a rollback, so you don't have to worry > about the integrity of your data.
I can use any I like, I wont have not even built the DB yet, so I am open to any suggestions. I don't see how I can do it all in one transaction, what does that mean? I think you may mean something like rather than doing 100,000 separate inserts, somehow build that into just one insert string, so it is then one connection? Can you elaborate? > "Is it then possible to have a SQL statement that would somehow take the > lump field data, select it, chop it up, and insert 100,000 rows, in this > case, three fields per row?" > > I haven't heard of a SQL function like that. In addition, you'll suffer > the pain twice by inserting it once as one big field, selecting it out > again, breaking it up, and then re-inserting it. I was thinking that perhaps MySql would be more efficient at it than some server side "middleware". > Is the code going to be running on the machine with the database? That > could improve it. The code that would do the insert will run on hardware that is on the same network as MySql, but certainly not the same machine, they are 2 different OS's so this is not possible to run them on the same machine. > One other thing to consider is to use "IMPORT DATA" to do a bulk load > rather than a tonne of insert statements. You can do this from a > command-line on the machine where the MySQL server is installed. See the > docs at http://www.mysql.com to get the syntax of the IMPORT DATA - it > should work on the format of the file as you specified below. Can import data be used on MySql if the data is not on the same machine as MySql? -- ------------------------------------------------------------- Scott Haneda Tel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED] Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
