What that means is you do 100,000 inserts, and then do one commit at the end. If the connection to the database dies, or the database itself crashes, then all the rows inserted will be rolled back, and no data will be in your database.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?
No, you still have to do all the inserts, but either they all get in, or none of them get in, depending on what happens as you are inserting.
"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".
I haven't seen a function like that; it might exist. It might not.
Not sure I follow. If you wrote it in C, and compiled it on the machine where teh MySQL database was.... or if you wrote it in Java, it could run anywhere. You can also write it in PERL and run it on the database machine, assuming you install PERL.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.
I don't think so. But IMPORT DATA doesn't require any coding. For example, you just put this into a file: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?
LOAD DATA LOCAL INFILE 'file_with_all_the_data.txt' INTO table the_table_where_rows_go FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '' LINES TERMINATED BY '\r\n';
This reads a file, and breaks each line up by a \t (and the line ends with \r\n in this example). It just dumps all the data into the table. The columns in the table have to be in the same order as the fields in each line.
No coding required.
David
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
