Solved

MYSQL

Posted on 2016-09-09
11
41 Views
Last Modified: 2016-09-30
how to insert 100000 data in 1 minutes in mysql table
0
Comment
Question by:vijendra solanki
  • 5
  • 4
11 Comments
 
LVL 9

Expert Comment

by:Brian Tao
ID: 41791222
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. ^^
0
 
LVL 24

Accepted Solution

by:
Tomas Helgi Johannsson earned 500 total points
ID: 41793937
Hi!

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.
http://www.geeksengine.com/database/data-manipulation/bulk-insert.php
http://dev.mysql.com/doc/refman/5.6/en/insert-speed.html
https://dev.mysql.com/doc/refman/5.6/en/optimizing-innodb-bulk-data-loading.html
http://www.journaldev.com/2494/jdbc-batch-insert-update-mysql-oracle
http://derwiki.tumblr.com/post/24490758395/loading-half-a-billion-rows-into-mysql

Regards,
    Tomas Helgi
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41801222
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
    from
    SingleDigits as d1,
    SingleDigits as d2,
    SingleDigits as d3,
    SingleDigits as d4
)
SELECT * FROM Series 




--

Open in new window

0
 
LVL 9

Expert Comment

by:Brian Tao
ID: 41823238
I disagree closing by accepting Pawan Kumar Khowal's comment, because WITH doesn't even work in MySQL (and the question is for MySQL).
0
 
LVL 9

Expert Comment

by:Brian Tao
ID: 41823243
I would propose to split the points to me (Brian Tao) and TomasHelgi, as the 2 solutions actually work.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41823247
Ohh my bad ! thought it was SQL Server.
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41823768
Disagree. Brain Tao solutions are not good (Loop oriented and not managed) . I think points should go to TomasHelgi.
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41823780
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.
0
 
LVL 9

Expert Comment

by:Brian Tao
ID: 41823878
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~
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41823907
Dear Brain,

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

Bye !
1

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Mysql not caching queries 4 50
mySql Syntax 7 33
get the data all row not only one row php 4 36
Mysql Crashing Intermittently 16 49
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now