Complicated/Simple SQL Insert: adding multiple rows

Question

I have a table connecting principals to their roles. I have come upon a situation where I need to add a role for each user. I have a statement SELECT id FROM principals which grabs a list of all the principals. What I want to create is something like the following:

INSERT INTO role_principal(principal_id,role_id)
VALUES(SELECT id FROM principals, '1');

so for each principal, it creates a new record with a role_id=1. I have very little SQL experience, so I dont know if I can do this as simply as I would like to or if there is some sort of loop feature in SQL that I could use.

Also, this is for a mySQL db (if that matters)

Answer 1

Use VALUES keyword if you want to insert values directly. Omit it to use any SELECT (where column count and type matches) to get the values from.

INSERT INTO role_principal(principal_id,role_id)
    (SELECT id, 1 FROM principals);

Answer 2

To avoid duplicates is useful to add a subquery :

INSERT INTO role_principal(principal_id,role_id) (SELECT id, 1 FROM principals p WHERE NOT EXISTS (SELECT * FROM role_principal rp WHERE rp.principal_id=p.id AND role_id=1) )