2

In MySQL we are able to insert multiple rows with the code

INSERT INTO tbl_name
    (a,b,c)
VALUES
    (1,2,3),
    (4,5,6),
    (7,8,9);

Is there any way to insert multiple rows in a single MySQL Stored procedure.

Create Stored Procedure:

CREATE PROCEDURE insertTbl_name(IN a_val int(10),IN b_val int(10),IN c_val int(10))
BEGIN
    INSERT INTO tbl_name
(a,b,c) VALUES (a_val ,b_val ,c_val);
END

Call Stored Procedure:

CALL insertTbl_name(1,2,3)
Sphinx
  • 956
  • 7
  • 21
  • check this: https://stackoverflow.com/questions/452859/inserting-multiple-rows-in-a-single-sql-query – Jenish Sep 22 '17 at 14:27
  • The problem starts earlier: you cannot easily pass several rows to the procedure (or return them). You would usually use a temporary table for that - and then you can simply do `insert ... select ... from temptable`. – Solarflare Sep 22 '17 at 14:27
  • @Jenish That question does not answer the above query. There is no problem with adding multiple rows in a single query. The issue is when trying to add multiple rows within a 'Stored Procedure' – Sphinx Sep 27 '17 at 07:01
  • @Solarflare I am looking for the optimum way to do upload tons of data in a secure manner.Why use a temp table if we can just insert one by one into the main table and minimise the process time – Sphinx Sep 27 '17 at 07:03
  • Yes. You can just insert one by one. But you do not want to insert one by one. Using a temptable for just one row is obviously overkill, but the default way to pass an "array". But maybe take a step back and check where your data comes from. If you e.g. create it in php, you can just execute that procedure for every row, or just do whatever the procedure is going to do directly (assuming permissions). If you have it in e.g. a csv, `load data infile` into a temp (or final) table is much faster anyway. See also [here](https://stackoverflow.com/q/8149545/6248528), maybe it gives some inspiration. – Solarflare Sep 27 '17 at 09:21

0 Answers0