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)
Improve this question
5
  • check this: stackoverflow.com/questions/452859/… Commented Sep 22, 2017 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. Commented Sep 22, 2017 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' Commented Sep 27, 2017 at 7: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 Commented Sep 27, 2017 at 7: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, maybe it gives some inspiration. Commented Sep 27, 2017 at 9:21
Related questions
0
Insert multiple rows into a table
0
How to do MySQL Multi-insert from select statement
1
How to write a stored procedure to insert values in multiple tables
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.