In MySQL, can we insert records into two tables simultaneously using stored procedures?

Yes, you can use stored procedures to insert into two tables in a single query. Let us first create a table −

mysql> create table DemoTable
(
   StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   StudentFirstName varchar(20)
);
Query OK, 0 rows affected (0.56 sec)

Here is the query to create the second table −

mysql> create table DemoTable2
(
   ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   ClientName varchar(20),
   ClientAge int
);
Query OK, 0 rows affected (0.76 sec)

Following is the query to create the stored procedure to insert into the two tables created above −

mysql> DELIMITER //
   mysql> CREATE PROCEDURE insert_into_twoTables(name varchar(100),age int)
      BEGIN
         INSERT INTO DemoTable(StudentFirstName) VALUES(name);
         INSERT INTO DemoTable2(ClientName,ClientAge) VALUES(name,age);
      END
      //
   Query OK, 0 rows affected (0.14 sec)
mysql> DELIMITER ;

Now call the stored procedure with the help of CALL command -

mysql> call insert_into_twoTables('Tom',38);
Query OK, 1 row affected, 1 warning (0.41 sec)

Check whether the records are inserted into both tables.

The query to display all the records in the first table is as follows-

mysql> select *from DemoTable;

This will produce the following output-

+-----------+------------------+
| StudentId | StudentFirstName |
+-----------+------------------+
| 1         | Tom              |
+-----------+------------------+
1 row in set (0.00 sec)

The following is the query to display all the records in the second table :

mysql> select *from DemoTable2;

This will produce the following output -

+----------+------------+-----------+
| ClientId | ClientName | ClientAge |
+----------+------------+-----------+
| 1        | Tom        | 38        |
+----------+------------+-----------+
1 row in set (0.00 sec)

The above is the detailed content of In MySQL, can we insert records into two tables simultaneously using stored procedures?. For more information, please follow other related articles on the PHP Chinese website!