How can I limit the number of records inserted on an INSERT INTO at one time

Go To StackoverFlow.com

1

I want to do an insert into my database like this. But the Table's may have 200k+ records in them. I want to split up the inserts into groups of 10,000 or so, how can I do this efficiently in MySQL only?

INSERT INTO `slugs` (`sku`, `tablename`)
select `SKU`, 'tableA'
from `tableA`
WHERE `SKU` NOT IN
(select `sku` from `slugs` where `tablename` = 'tableA');

Example Code: http://sqlfiddle.com/#!2/a75a1/8

2012-04-03 21:57
by Brad
Why do you want to split up the inserts - mellamokb 2012-04-03 21:59
It's going very slow, and I was told if I split it up it into multiple transactions it would probably speed things up - Brad 2012-04-03 22:09


1

Add a LIMIT 10000 to the end and run it until rows inserted = 0.

If it is not a one shot, you can do this in a procedure with a loop..

2012-04-03 22:11
by barsju


0

Try this:

SET @rank=0;
select rank
, IF(MOD(rank,10000)=0,CONCAT(IF(MOD(rank,10000)=1,CONCAT("insert ignore into table values (""",NAME,""",""",SKU,""")"),CONCAT(",(""",NAME,""",""",SKU,""")")),";"),IF(MOD(rank,10000)=1,CONCAT("insert ignore into table values (""",NAME,""",""",SKU,""")"),CONCAT(",(""",NAME,""",""",SKU,""")"))) as insert_statement
from (
SELECT @rank:=@rank+1 AS rank,NAME,SKU
from tablea 
) der
2015-07-13 22:23
by tomb