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

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 |
| ...|   ...  |

SELECT * FROM store

-------------------------------
| 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!