How to embed massive data into a database at the same time?

Question

I created a stored procedure. I get a cursor with a huge amount of data (about 1 row). After that, I call another procedure in it to do all the calculations associated with the necessary data. I create a temporary table and try to insert this calculated data into it. But it takes too long about 9.5 minutes. I want to know how to embed voluminous data using the smallest "INSERT" queries since 1L insert queries cause the lowest performance. Can anyone help me out?

Answer 1

You can use the following SQL statement for bulk insertion:

INSERT INTO TABLE_A (A, B, C, D) VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4); 

Answer 2

Your question is a bit vague, but you can BULK load data into mysql using

 load data infile... 

Check out the following links:

• http://dev.mysql.com/doc/refman/5.1/en/load-data.html
• MySQL loading data infile - acceleration?

If you need to process data during loading, it is better to first load the mass into a temporary table and then run some stored procedures that process and populate your main tables.

Hope this helps.

Answer 3

You can also insert a record into a table in memory. After fully inserting into the memory table, the following code to insert many rows from the table into memory.

 INSERT INTO TABLE_A (A, B, C, D) SELECT A,B,C,D FROM INMEMORY_TABLE DELETE FROM INMEMORY_TABLE