Insert values in two tables with a single stored procedure call in MySQL

Following is the syntax to insert values in two tables with a stored procedure −

DELIMITER //
CREATE PROCEDURE yourProcedureName(anyVariableName int)
   BEGIN
   insert into yourTableName1(yourColumnName1) values(yourVariableName);
   insert into yourTableName2(yourColumnName2) values(yourVariableName);
   END
//

Let us first create a table −

mysql> create table DemoTable1
   -> (
   -> StudentScore int
   -> );
Query OK, 0 rows affected (0.58 sec)

Following is the second table −

mysql> create table DemoTable2
   -> (
   -> PlayerScore int
   -> );
Query OK, 0 rows affected (0.52 sec)

Here is the query to create a stored procedure and insert values in two tables −

mysql> DELIMITER //
mysql> CREATE PROCEDURE insert_proc(value int )
   -> BEGIN
   -> insert into DemoTable1(StudentScore) values(value);
   -> insert into DemoTable2(PlayerScore) values(value);
   -> END
-> //
Query OK, 0 rows affected (0.16 sec)
mysql> DELIMITER ;

Now you can call the stored procedure using CALL command −

mysql> call insert_proc(89);
Query OK, 1 row affected (0.29 sec)

Display all records from both the tables using select statement −

mysql> select * from DemoTable1333;
+--------------+
| StudentScore |
+--------------+
|           89 |
+--------------+
1 row in set (0.00 sec)
mysql> select * from DemoTable1334;
+-------------+
| PlayerScore |
+-------------+
|          89 |
+-------------+
1 row in set (0.00 sec)

AmitDiwan

Published on 05-Nov-2019 06:56:13

• Related Questions & Answers
• MySQL select and insert in two tables with a single query
• Can we use stored procedure to insert records into two tables at once in MySQL?
• How can a MySQL stored procedure call another MySQL stored procedure inside it?
• Insert data in a table in MySQL stored procedure?
• Call Stored Procedures within a Stored Procedure with IF Logic?
• How to set two variables in a stored procedure with a single MySQL select statement?
• How to correctly use delimiter in a MySQL stored procedure and insert values?
• How to insert into two tables using a single MySQL query?
• Use UNION ALL to insert records in two tables with a single query in MYSQL
• How can I create a stored procedure to insert values in a MySQL table?
• How to call a stored procedure using select statement in MySQL?
• MySQL SELECT from two tables with a single query
• MySQL Stored Procedure calling with INT and VARCHAR values
• Create a stored procedure with delimiter in MySQL
• MySQL Stored procedure to declare two values and perform mathematical operation