How to Insert Into Multiple Tables in MySQL
This tutorial exemplifies transactions and stored procedures to insert into multiple tables in MySQL.
Insert Into Multiple Tables in MySQL
There is no way to insert a single MySQL command into multiple tables, but we can use MySQL transactions to meet the project requirements.
Let’s create two tables named users and user_profiles. The users table has three attributes, user_id, user_name and user_password, while the profiles table contains user_id, user_bio and homepage as attributes.
See the following commands that we use to create both tables.
Example Code:
CREATE TABLE users(
user_id INT NOT NULL AUTO_INCREMENT,
user_name VARCHAR(45) NOT NULL,
user_password VARCHAR(45) NOT NULL,
PRIMARY KEY(user_id)
);
CREATE TABLE user_profiles(
user_id VARCHAR(45) NOT NULL,
user_bio VARCHAR(45) NOT NULL,
homepage VARCHAR(50) NOT NULL
);
Here, we have created both tables. Now, we can insert data in both tables at once in the following way.
Remember, the value of user_profiles.user_id and users.user_id is the same.
Example Code:
BEGIN;
INSERT INTO users (user_id,user_name, user_password)
VALUES (2,'username2', 'userpassword2');
SELECT @UserID := MAX(user_id) FROM users;
INSERT INTO user_profiles (user_id, user_bio, homepage)
VALUES(@UserID,'this is bio for username2', 'http://www.username2.com');
COMMIT;
Add two records and use the SELECT statement to see the results.
Output (for the users table):
+---------+-----------+---------------+
| user_id | user_name | user_password |
+---------+-----------+---------------+
| 1 | username1 | userpassword1 |
| 2 | username2 | userpassword2 |
+---------+-----------+---------------+
2 rows in set (0.03 sec)
Output (for the user_profiles):
+---------+---------------------------+--------------------------+
| user_id | user_bio | homepage |
+---------+---------------------------+--------------------------+
| 1 | this is bio for username1 | http://www.username1.com |
| 2 | this is bio for username2 | http://www.username2.com |
+---------+---------------------------+--------------------------+
2 rows in set (0.00 sec)
Alternatively, we can create a stored procedure as follows to save time and effort.
Example Code:
DELIMITER ;;
CREATE PROCEDURE InsertALL()
BEGIN
INSERT INTO users (user_id,user_name, user_password)
VALUES (3,'username3', 'userpassword3');
SELECT @UserID := MAX(user_id) FROM users;
INSERT INTO user_profiles (user_id, user_bio, homepage)
VALUES(@UserID,'this is bio for username3', 'http://www.username3.com');
END ;;
DELIMITER ;
