In MySQL DB, how do I create a loop to insert such data?

Question

I need to insert thousands of rows in a table in MYSQL DB, by the given index value, such as:

"
insert into Table_1 ( column_A, column_B ) values ( 'A0001', 'B0001');
insert into Table_1 ( column_A, column_B ) values ( 'A0002', 'B0002');
insert into Table_1 ( column_A, column_B ) values ( 'A0003', 'B0003');
insert into Table_1 ( column_A, column_B ) values ( 'A0004', 'B0004');
....
insert into Table_1 ( column_A, column_B ) values ( 'A9999', 'B9999');
".

How do I do such in a loop way in MySQL DB ?

Thanks a lot !

Jack

how you obtain the values for insert .. (external files?? query from another table?) — ScaisEdge, Mar 31 '19 at 19:48
This is an example of how to use a loop in MySQL [https://stackoverflow.com/questions/5125096/for-loop-example-in-mysql#5126655](https://stackoverflow.com/questions/5125096/for-loop-example-in-mysql#5126655) — Mo'ath Zaghdad, Mar 31 '19 at 19:54
Thost values are originated from the index value, starring from 1, 2, 3, ... till 9999. and I need to insert that many rows in that table. I am looking for a loop way to do that. — user3595231, Mar 31 '19 at 19:59
@user3595231: The client (version) doesn't matter, what's the server version? You can use `select version();` to find out. — sticky bit, Apr 01 '19 at 03:42
Any chance you can update to at least 10.2.2? That would support recursive CTEs, which could make this very easy. — sticky bit, Apr 01 '19 at 18:34

score 0 · Answer 1 · answered Mar 31 '19 at 20:02

SQL does not work that way (mostly). One SQL statement executes against a set of data. However, you can certainly bulk insert, like so:

insert into Table_1 ( column_A, column_B ) values ( 'A0001', 'B0001'), ( 'A0002', 'B0002'), ( 'A0003', 'B0003'), ( 'A0004', 'B0004');

if you are including all columns in the table, in order, you can omit the column names portion like so:

insert into Table_1 values ( 'A0001', 'B0001'), ( 'A0002', 'B0002'), ( 'A0003', 'B0003'), ( 'A0004', 'B0004');

If you really need to loop, you can create a stored procedure to do so. Stored procedures have a lot of uses, but generally there is another reason to use a stored procedure than just looping.

Sorry I was looking at "loop" "store procedure" way, but I am not sure is how to generate the concatenated column values, such as 1 becomes "A0001", while 9999 becomes "A9999" ? — user3595231, Mar 31 '19 at 20:10

In MySQL DB, how do I create a loop to insert such data?

1 Answers1