Query for Insert 1000000 rows in one query using mysql

Question

I have to Insert 1000000 rows with same values for testing purpose.

This is the query for insert one row.

insert into CRM_test.CRM_Pipeline(Name,BranchId,CreatedOn,CreatedFrom ) values("Pipeline1",1, now(),95);

Is there any ways to Insert 1000000 rows in one query using mysql?

Thanks in advance

Yes, use a loop. https://stackoverflow.com/q/5125096/943730 – Jonathan Wilson Dec 05 '18 at 06:00 — Jonathan Wilson, Dec 05 '18 at 06:00

Tim Biegeleisen · Answer 1 · 2018-12-05T06:08:14.293

In theory, there is no limit to how much data you can insert via an INSERT statement. But, in practice you may be limited by the max_allowed_packet, which defaults to 1MB. One option would be to temporarily increase max_allowed_packet to a value which can support inserting 1 million records. An alternative to this might be to use LOAD DATA with a CSV file containing your data.

One motivation for not doing a single insert is that, given the large size of the insert, should it fail somewhere in the middle, e.g. due to network issues, you could either lose the entire transaction, or end up with a partial insert. So, you might want to break your insert into smaller batches, if for no other reason than it makes it safer.

cmb28 · Answer 2 · 2018-12-05T06:26:20.677

I don't know if this will work for 1000000 rows,
If not you can reduce the number to a lesser value and run query multiple times.
create this stored procedure and execute it.

CREATE PROCEDURE addRows()
BEGIN
    DECLARE i int DEFAULT 1;
    WHILE i <= 1000000 DO
         insert into CRM_test.CRM_Pipeline(Name,BranchId,CreatedOn,CreatedFrom ) values("Pipeline1",1, now(),95);
        SET i = i + 1;
    END WHILE;
END

Query for Insert 1000000 rows in one query using mysql

2 Answers2