How do I do a multiple insert?

I have two tables, Person and Role.

I want to search the Person table, and find everyone that lives in Oakland. If they live in Oakland, assign them a role of Manager in the PersonRole table.

Because the real data set has 100 rows in the person table, I would strongly prefer that the query or queries that run searches the Person table first, to retrieve the Person_ID values and then somehow does a multiple insert with the retrieved data.

SO, Please help!

Sample Data

Person Table

Person_ID....Name........City

0................开发者_运维知识库...Dave........Oakland

1...................Jane........Oakland

2...................Steve.......San Francisco

Role Table

Role_ID....Role

0..............Manager

1..............Assistant

PersonRole

Person_ID......Role_ID

INSERT INTO PersonRole
            (Person_ID,
             Role_ID)
SELECT Person_ID,
       0 AS Role_Id
FROM   Person
WHERE  City = 'Oakland';

You can add

EXCEPT
SELECT Person_ID,
       Role_ID
FROM PersonRole      

Onto the end if some Oakland residents might already be managers (Assuming SQL Server 2005+).

If you add a left join to Person Role, you can make sure the record doesn't already exist.

INSERT INTO PersonRole 
  (PersonID, 
   Role_ID)
SELECT
   p.Person_ID,
   0
FROM Person p
LEFT JOIN PersonRole pr ON 
   pr.Person_ID = p.Person_ID 
   AND pr.Role_ID = 0  --Make sure the record doesn't already exist
WHERE 
   p.City = 'Oakland' 
   AND pr.Person_ID IS NULL --Make sure the record doesn't already exist