Sometimes I want to insert multiple rows in a table based on the result of a specific SELECT statement. For example, recently I wanted to add multiple ITEM id's and a static TEAM id '7' to a table. A single INSERT would look like this:
INSERT INTO TABLE (item_id, team_id) values (201050, 7);
Doing this once or twice is fine, but I wanted to add 178 unique ITEM id's based on query in the order of 'SELECT id from ITEMS ....'. Luckily, Mysql offers a syntax to do this.
INSERT INTO TABLE (item_id, team_id)
SELECT i.id, 7 FROM ITEMS i WHERE <SOME_CONDITION>;
This will execute an INSERT statement for each ITEM id returned by the SELECT statement along with the static value '7' as supplied in the query.