Insert a record in database for each result from a SQL query

Reading time ~2 minutes

Just a little trick

Nothing too much elaborate! This is a little useful trick to insert a record in a table for each result from a SQL query. It comes in handy for example when you have to manually populate intermediate table, in case of a many-to-many relationship.

Let’s take a basic example. Suppose that you have a list of products in your database and these products are related to a number of online stores.

SELECT * FROM product

| ID | Name   |
|  1 | Pen    |
|  2 | Pencil |
| ...|   ...  |

| ID | Name      | Address    |
|  1 | Best Buy  | First str. |
|  2 | Walmart   | Second str.|
| ...|     ...   |    ...     |

A product can belong to a multiple stores, but a store can have many products, thus the relation must use an intermediate store_has_product table.

Now suppose that you need to add a new store and to associate all the products it. The first operation is obviously

INSERT INTO store (name, address) VALUES ('New Store', 'Nth str.')

In case of hundreds of products, to build the relationships with the other table you simply cannot use the manual statement. Instead the idea is to take all the items in the product table and insert them along with the ID of the new store. The quickest way is the following:

INSERT INTO store (store_id, product_id)
SELECT <new store id>, id FROM product;

Simple and effective!

comments powered by Disqus

Log Route calls in Laravel using Middleware


Sometimes when developing a new site or application in Laravel you need to log exactly the route calls in order to track …