I've created a stored procedure. I fetch cursor with huge amount of data(around 1L rows). After that I call another procedure in it to do all calculations related to needed data. I create temporary table and try to insert this calculated data in it. But it takes too long about 9.5 mins. I want to know how to insert bulk data by using least "INSERT" queries as 1L insert queries cause poorest performance. Can anyone help me??
1
-
@Jack 1L = 1 Lakh = 100,000 en.wikipedia.org/wiki/LakhJohn Carter– John Carter2012-10-18 18:31:15 +00:00Commented Oct 18, 2012 at 18:31
Add a comment
|
3 Answers
You can use the following SQL statement for bulk insert:
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);
Comments
Your question is a bit vague but you can BULK load data into mysql using
load data infile...
Check the following links:
If you need to process the data during the load it might be better to first bulk load the data into a temporary table then run some stored procedures against it which process and populate your main tables.
Hope this helps.
Comments
also you can insert record to in-memory table.after complete inserting to in-memory table following code for insert many rows from in-memory table.
INSERT INTO TABLE_A (A, B, C, D)
SELECT A,B,C,D FROM INMEMORY_TABLE
DELETE FROM INMEMORY_TABLE
