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
精彩评论