how to insert 100000 data in 1 minutes in mysql table
vijendra solankiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian TaoSenior Business Solutions ConsultantCommented:
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. ^^
Tomas Helgi JohannssonDatabase Administrator / Software EngineerCommented:

To insert massive amount of data in the shortest time as possible the best way to do that is to
use batch insert / bulk data loading using your favorite programming language. A size of 1000 rows/batch is a good starting point and usually gives you the best performance although in some cases smaller or larger batch size is better depending on your data and/or workload.

There are several parameters you will need to adjust like max_allowed_packet to a size to fit your batch size.

See the following urls for more info.

    Tomas Helgi

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Pawan KumarDatabase ExpertCommented:
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

Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Brian TaoSenior Business Solutions ConsultantCommented:
I disagree closing by accepting Pawan Kumar Khowal's comment, because WITH doesn't even work in MySQL (and the question is for MySQL).
Brian TaoSenior Business Solutions ConsultantCommented:
I would propose to split the points to me (Brian Tao) and TomasHelgi, as the 2 solutions actually work.
Pawan KumarDatabase ExpertCommented:
Ohh my bad ! thought it was SQL Server.
Pawan KumarDatabase ExpertCommented:
Disagree. Brain Tao solutions are not good (Loop oriented and not managed) . I think points should go to TomasHelgi.
Pawan KumarDatabase ExpertCommented:
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.
Brian TaoSenior Business Solutions ConsultantCommented:
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~
Pawan KumarDatabase ExpertCommented:
Dear Brain,

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

Bye !
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.