Link to home
Start Free TrialLog in
Avatar of vijendra solanki
vijendra solanki

asked on


how to insert 100000 data in 1 minutes in mysql table
Avatar of Brian Tao
Brian Tao
Flag of Taiwan, Province of China image

You have several options, among them here are 2 that I can think of
1) Use a program or a script to do the insert.
2) Manually prepare those 100000 SQL insert statements, paste them into whatever UI you're using and run.  Believe me, they will execute and finish in less than 1 minute. ^^
Avatar of Tomas Helgi Johannsson
Tomas Helgi Johannsson
Flag of Iceland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Please use below


;WITH SingleDigits(Number) AS
    SELECT Number
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
    (9), (0)) AS X(Number)
,Series AS
    SELECT (d1.Number+1) + (10*d2.Number) + (100*d3.Number) + (1000*d4.Number) Number
    SingleDigits as d1,
    SingleDigits as d2,
    SingleDigits as d3,
    SingleDigits as d4


Open in new window

I disagree closing by accepting Pawan Kumar Khowal's comment, because WITH doesn't even work in MySQL (and the question is for MySQL).
I would propose to split the points to me (Brian Tao) and TomasHelgi, as the 2 solutions actually work.
Ohh my bad ! thought it was SQL Server.
Disagree. Brain Tao solutions are not good (Loop oriented and not managed) . I think points should go to TomasHelgi.
Even If i change "With" from a SELECT my method would be the best one. Even with in few seconds it will insert the 100000 records.
I'm fine if all 500 points goes to Tomas Helgi, but I do have something to say in response to Pawan Kumar Khowal's last 2 comments:
  1. I don't see why "loop oriented" is not good.  It solves the problem.
  2. Pawan Kumar Khowal's method doesn't work in MySQL as "WITH" is not supported, but the question is for MySQL.
  3. Not only does it not work in MySQL, but also it doesn't answer OP's question at all!  There's nothing mentioning that the OP wants to insert 0 to 9999 (or you may modify the SQL code to insert 99999, whatever) into the table. He wants to insert 100000 data, remember that? It may be 100000 names, 100000 addresses or 100000 who knows what.  The method "would be the best one"? Oh well~
Dear Brain,

Lets just close this. Technical discussions ... :)

Bye !