MySQL : INSERT INTO with foreach like

Question

i want to do a query like this :

INSERT INTO `gesdoc_docs_authorizations`
    (`gesdoc_docs_id`, `id_profil_do`, `autorisation`, `personal_doc`)
VALUES (
    (SELECT `id`
    FROM `gesdoc_docs`),
    (SELECT `id`
    FROM `users_profil_do`),
    1, 0
   );

Table gesdoc_docs has 40 rows and users_profil_do has 700 rows. I need to have for each row in users_profil_do the 40 rows of andco_gesdoc_docs associated (so normally my query will insert 28,000 rows). Is it possible ? How can i do that with a mysql query ?

Thanks for help :)

Answer 1

You don't use the VALUES keyword when inserting from a SELECT.

INSERT INTO gesdoc_docs_authorizations
    (`gesdoc_docs_id`, `id_profil_do`, `autorisation`, `personal_doc`)
SELECT g.id, d.id, 1, 0
FROM gesdoc_docs g
JOIN users_profil_do d

If you don't use an ON clause to specify a join condition, it treats all rows as matching, which results in a full cartesian product of the two tables.