1

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??

Shrikant
  • 259
  • 2
  • 4
  • 13

3 Answers3

8

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);
Andrew Landsverk
  • 635
  • 7
  • 17
2

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.

Community
  • 1
  • 1
Jon Black
  • 15,289
  • 5
  • 40
  • 41
0

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
MajidTaheri
  • 3,288
  • 6
  • 24
  • 43