sql - Performing multiple inserts for a single row in a query -

i have table containing data need migrate table linking table. 1 time migration part of upgrade.

i have company table contains records relating company , contact person. want migrate contact details table , link new person linking table

consider have table populated

tblcompany

• companyid
• companyname
• regno
• contactforename
• contactsurname

and want migrate contact person data to

tblperson

• personid (identitycolumn)
• forename
• surname

and use identity column resulting , insert linking table

tblcompanyperson

• companyid
• personid

i've tried few different ways approach using cursors , output variables temp table none seem right me (or give me solution...)

the closest have got have companyid on tblperson , insert companyid , output new personid , companyid temp table. loop through temp table create tblcompanycontact.

example

declare @companycontact table (companyid int, personid int)  insert tblperson     (forename,     surname,     companyid) output inserted.companyid, inserted.personid @companycontact select       contactpersonforename,      contactpersonsurename,       companyid tblcompany c   insert tblcompanyperson     (companyid,     personid) select c.companyid, personid @companycontact c 

background

• im using ms sql server 2008 r2
• the tblperson populated hundreds of thousands of records.

there 'trick' using merge statement achieve mapping between newly inserted , source values:

merge tblperson trgt using tblcompany src on 1=0 when not matched  insert      (forename, surename)     values (src.contactpersonforename, src.contactpersonsurename) output  src.companyid, inserted.personid  tblcompanyperson (companyid, personid); 

that 1=0 condition source. might want replace or whole source sub-query check whatever have same person mapped.

edit: here reading using merge , output