CODEWITHSUNDEEP
mysqlstored-proceduresinsert
usumoio
usumoio

Reputation: 3568

How to do MySQL Multi-insert from select statement

So here are the basics of my question. I have selected a result set from the join of two tables. This result set is a series of id values and email addresses. Now for each row in this result set, I want to insert a new row into a third table called timestamps that has a timestamp from NOW() and the id value from the result set, but not the email address. I know I can achieve this by running a loop within a stored procedure. Additionally I think I cannot simply run an insert into statement because the data I am inserting into timestamps does not come from my result set in its entirety, my result set data only decides the number of rows that must be inserted and the ids, while I need to return those email addresses for other processing. I am searching for a clever way to avoid using a loop for this. Is that possible? Can I generate a dynamic insert statement? Thank you to anyone who gives time or help.

Upvotes: 0

Views: 137

Answers (1)

Ankit Gupta
Ankit Gupta

Reputation: 189

Suppose:

table1 with columns: id

table2 with columns: id, email

table1 and table2 are linked with column id

timestamps with columns: id, time

Then query will be:

insert into timestamps(id,time) select table1.id,now() from table1 inner join table2 on table1.id = table2.id;

Upvotes: 1

Related Questions