Insert records from one table to multiple relational tables using MySQL

Question

I have three tables customers, customer_entity, customer_info. I wanted to insert records from customers table to customer_entity and customer_info at same time, but customer_entity tables primary key will be part of customer_info table.

Assumed Code can we write something like this?

INSERT INTO customer_entity (mobile, name)
INSERT INTO customer_info (customer_entity_id,email, name)
SELECT mobile, name, email customers FROM customers

I dont want to use any programming language only MYSQL

I am not able to get customer_entity_id for customer_info table from customer_entity table. — Firoz Tennali, Feb 19 '19 at 12:46
"not able to" isn't an error message or problem statement. You could give us a clearer description of the issue. — ADyson, Feb 19 '19 at 12:49
The code which I have provided is not tested, it is just my assumption, can we do something like this. — Firoz Tennali, Feb 19 '19 at 12:50
But basically I think you want to know how to get the ID of the last record inserted into customer_entity, so you can use it in the next INSERT statement? Is that right? If so then [last_insert_id()](https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-insert-id) is the solution you need. I'm fairly sure you could have googled that yourself...it's a _very_ common scenario. — ADyson, Feb 19 '19 at 12:50
Possible duplicate of [Get the new record primary key ID from mysql insert query?](https://stackoverflow.com/questions/17112852/get-the-new-record-primary-key-id-from-mysql-insert-query) — ADyson, Feb 19 '19 at 12:53
@ADyson can you explain it with this scenario how can I put the last inserted id of customer_entity table — Firoz Tennali, Feb 19 '19 at 12:54
Put it in the place you need it. i.e. just use the last_insert_id() function in place of `customer_entity` in your insert statement. Or if you want to be neater, read it into a variable first and then pass that variable to the insert statement. Again, you can research this 100 times over online. — ADyson, Feb 19 '19 at 12:55
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/188718/discussion-between-firoz-tennali-and-adyson). — Firoz Tennali, Feb 20 '19 at 04:19

score 0 · Answer 1 · answered Feb 20 '19 at 05:33

This query may help you.

INSERT INTO customer_entity (mobile,email)
SELECT mobile, email FROM customer ORDER BY id ASC;
INSERT INTO customer_info (customer_entity_id, email)
SELECT customer_entity.id, email, (SELECT email FROM customer WHERE mobile= customer_entity.mobile) FROM customer;

Here the customer table is the main table which has data already and we are inserting it into customer_entity table and customer_info table with customer_entity_id.

score 0 · Answer 2 · answered Feb 20 '19 at 09:12

You can try using SELECT LAST_INSERT_ID() also:

INSERT INTO customer_entity (mobile,email)
INSERT INTO customer_info (LAST_INSERT_ID(), email)

Docs

If you insert a record into a table that contains an AUTO_INCREMENT column, you can obtain the value stored into that column by calling the mysql_insert_id() function.

Insert records from one table to multiple relational tables using MySQL

2 Answers2