Select from a table and Insert new records into another table but change some values

I have a 1 to many relationship between two tables... say Organisation and Members

I want to create a new Organisation based on an existing one (ID 111) and copy all the members but associate them with the newly created organisation.

some pseudo code..

-- Just create a new organisation based on the new name and address
-- passed to the proc.
Insert into Organisation (newOrganisationName, newAddress) 
returning Organisation_ID into v_orgID;

So now I have the new organisation id returned in v_orgID of say 999 and I want to copy the members from an开发者_运维技巧 existing Organisation with say an ID of 111 and associate these with the new OrgID.

What is the best way to achieve this... should I loop and insert or can I use the Insert Into - select from method

INSERT INTO Members (OrganisationID, Membername, MemberAddress)
(SELECT  v_orgID, MemberName, MemberAddress FROM Member
 WHERE  OrganisationId = 111)

thanks Mick

Just insert the v_orgID into the query as a number:

INSERT INTO ... (SELECT 999, MemberName, MemberAddress FROM Member WHERE  OrganisationId = 111)

For 999 as the new OrganizationId.

Use INSERT/SELECT, changing the value:

INSERT INTO Members (OrganisationID, Membername, MemberAddress)
SELECT  999, MemberName, MemberAddress FROM Member
 WHERE  OrganisationId = 111